Analytical and Statistical Queries

Daily Revenue

CREATE OR REPLACE FUNCTION get_daily_revenue(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(payment_date DATE, total_revenue NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT
        DATE(created_at) AS payment_date,
        SUM(amount) AS total_revenue
    FROM payments
    WHERE DATE(created_at) BETWEEN p_start_date AND p_end_date
    GROUP BY DATE(created_at)
    ORDER BY payment_date;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_daily_revenue('2025-01-01', '2025-01-31');

Daily Order Count and Average Order Value Report

CREATE OR REPLACE FUNCTION get_daily_order_stats(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(order_date DATE, total_orders BIGINT, avg_order_value NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT
        DATE(o.datetime) AS order_date,
        COUNT(o.id) AS total_orders,
        AVG(p.amount) AS avg_order_value
    FROM orders o
    JOIN payments p ON o.id = p.order_id
    WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date
    GROUP BY DATE(o.datetime)
    ORDER BY order_date;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_daily_order_stats('2025-01-01', '2025-01-31');

Revenue Split: Online vs. Tab Orders

CREATE OR REPLACE FUNCTION get_revenue_split(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(order_type TEXT, total_revenue NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT 'Online Orders' AS order_type, SUM(p.amount) AS total_revenue
    FROM orders o
    JOIN payments p ON o.id = p.order_id
    JOIN online_orders oo ON o.id = oo.order_id
    WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date

    UNION ALL

    SELECT 'Tab Orders' AS order_type, SUM(p.amount) AS total_revenue
    FROM orders o
    JOIN payments p ON o.id = p.order_id
    JOIN tab_orders tord ON o.id = tord.order_id
    WHERE DATE(o.datetime) BETWEEN p_start_date AND p_end_date;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_revenue_split('2025-01-01', '2025-01-28');

Top 10 Best-Selling Products

SELECT
    p.name AS product,
    SUM(oi.quantity) AS total_quantity_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.name
ORDER BY total_quantity_sold DESC
LIMIT 10;

Reservations by Day of Week

CREATE OR REPLACE FUNCTION get_reservations_by_day(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(day_of_week INT, total_reservations BIGINT) AS $$
BEGIN
    RETURN QUERY
    SELECT
        EXTRACT(DOW FROM datetime)::INT AS day_of_week,  -- 0=Sunday, 1=Monday, etc.
        COUNT(*) AS total_reservations
    FROM reservations
    WHERE DATE(datetime) BETWEEN p_start_date AND p_end_date
    GROUP BY day_of_week
    ORDER BY total_reservations DESC;
END;
$$ LANGUAGE plpgsql;

Average Shift Duration per Manager

SELECT
    manager_id,
    AVG(EXTRACT(EPOCH FROM (end_time - start_time)) / 3600) AS avg_shift_duration_hours
FROM shifts
GROUP BY manager_id;

Yearly Revenue Report

CREATE OR REPLACE FUNCTION get_yearly_revenue(p_start_date DATE, p_end_date DATE)
RETURNS TABLE(payment_year DATE, total_revenue NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT
        date_trunc('year', created_at)::DATE AS payment_year,*
        SUM(amount) AS total_revenue
    FROM payments
    WHERE DATE(created_at) BETWEEN p_start_date AND p_end_date
    GROUP BY date_trunc('year', created_at)
    ORDER BY payment_year;
END;
$$ LANGUAGE plpgsql;