Changes between Version 5 and Version 6 of AnalyticalStatisticalQuerying

Timestamp:

09/03/25 18:35:32 (8 weeks ago)

Author:

226030

Comment:

--

AnalyticalStatisticalQuerying

@@ -1 +1 @@
 == 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 $$
+**Monthly operations summary**
+{{{
+SELECT
+    dates.operation_date,
+    COUNT(DISTINCT r.id) as total_reservations,
+    COUNT(DISTINCT o.id) as total_orders,
+    COUNT(DISTINCT r.user_id) as unique_customers,
+    COUNT(DISTINCT fs.employee_id) as active_employees,
+    COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue
+FROM generate_series(
+                         CURRENT_DATE - INTERVAL '30 days',
+                         CURRENT_DATE,
+                         '1 day'::interval
+         ) dates(operation_date)
+         LEFT JOIN reservations r
+                   ON DATE(r.datetime) = dates.operation_date
+    LEFT JOIN orders o
+ON DATE(o.datetime) = dates.operation_date
+    LEFT JOIN order_items oi
+    ON o.id = oi.order_id
+    LEFT JOIN tab_orders to2
+    ON o.id = to2.order_id
+    LEFT JOIN front_staff fs
+    ON to2.front_staff_id = fs.employee_id
+GROUP BY dates.operation_date
+ORDER BY dates.operation_date DESC;
+}}}
+
+** 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(14,2)) 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;
+RETURN QUERY
+SELECT 'Online Orders'::text AS order_type,
+        COALESCE(SUM(p.amount), 0)::numeric(14,2) 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 o.datetime::date BETWEEN p_start_date AND p_end_date
+
+UNION ALL
+
+SELECT 'Tab Orders'::text AS order_type,
+        COALESCE(SUM(p.amount), 0)::numeric(14,2) 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 o.datetime::date BETWEEN p_start_date AND p_end_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
+}}}
+
+**Top 10 products by revenue **
+{{{
+SELECT
+    p.id as product_id,
+    p.name as product_name,
+    c.name as category_name,
+    SUM(oi.quantity) as total_quantity_sold,
+    SUM(oi.quantity * oi.price) as total_revenue,
+    ROUND(100.0 * SUM(oi.quantity * oi.price) / SUM(SUM(oi.quantity * oi.price)) OVER (), 2) as revenue_share_percent
+FROM products p
+         JOIN categories c ON p.category_id = c.id
+         JOIN order_items oi ON p.id = oi.product_id
+         JOIN orders o ON o.id = oi.order_id
+WHERE o.datetime >= CURRENT_DATE - INTERVAL '90 days'
+GROUP BY p.id, p.name, c.name
+ORDER BY total_revenue DESC
+    LIMIT 10;
+}}}
+
+**Revenue by shift period**
+{{{
+CREATE OR REPLACE VIEW v_revenue_by_shift_period AS
+WITH distinct_shift_periods AS (
+    SELECT DISTINCT
+        start_time::time AS start_t,
+        end_time::time AS end_t
+    FROM
+        shifts
+)
+SELECT
+    TO_CHAR(o.datetime, 'YYYY-MM') AS period,
+    dsp.start_t::text || '-' || dsp.end_t::text AS shift_period,
+    SUM(oi.price * oi.quantity) AS total_revenue
+FROM
+    orders o
+        JOIN
+    order_items oi ON o.id = oi.order_id
+        JOIN
+    distinct_shift_periods dsp ON o.datetime::time >= dsp.start_t AND o.datetime::time < dsp.end_t
+GROUP BY
+    period,
+    shift_period
+ORDER BY
+    period DESC,
+    shift_period ASC;
+}}}
+
+** Managers' shifts above monthly average revenue **
+{{{
+WITH manager_worked_shifts AS (
+    SELECT DISTINCT
+        s.id AS shift_id,
+        s.date,
+        s.start_time,
+        s.end_time,
+        a.employee_id AS manager_id
+    FROM assignments a
+             JOIN shifts s   ON s.id = a.shift_id
+             JOIN managers m ON m.employee_id = a.employee_id
+    WHERE s.date >= date_trunc('year', CURRENT_DATE)::date
+    AND s.date <  (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date
+    ),
+    shift_revenue AS (
+SELECT
+    mws.shift_id,
+    date_trunc('month', mws.date)::date AS month_start,
+    mws.date AS shift_date,
+    mws.start_time,
+    mws.end_time,
+    mws.manager_id,
+    COALESCE(SUM(oi.quantity * oi.price), 0)::numeric(14,2) AS shift_revenue
+FROM manager_worked_shifts mws
+    LEFT JOIN orders o
+ON o.datetime::date = mws.date
+    AND o.datetime::time >= mws.start_time
+    AND o.datetime::time <  mws.end_time
+    LEFT JOIN order_items oi ON oi.order_id = o.id
+GROUP BY
+    mws.shift_id, month_start, mws.date, mws.start_time, mws.end_time, mws.manager_id
+    ),
+    monthly_avg AS (
+SELECT month_start, AVG(shift_revenue)::numeric(14,2) AS avg_revenue_per_shift
+FROM shift_revenue
+GROUP BY month_start
+    )
+SELECT
+    to_char(sr.month_start, 'YYYY-MM') AS period,
+    sr.shift_id,
+    sr.shift_date,
+    sr.start_time AS shift_start_time,
+    sr.end_time   AS shift_end_time,
+    u.email       AS manager_email,
+    sr.shift_revenue,
+    ma.avg_revenue_per_shift,
+    (sr.shift_revenue - ma.avg_revenue_per_shift)::numeric(14,2) AS above_by
+FROM shift_revenue sr
+         JOIN monthly_avg ma ON ma.month_start = sr.month_start
+         JOIN managers m     ON m.employee_id = sr.manager_id
+         JOIN employees e    ON e.user_id     = m.employee_id
+         JOIN users u        ON u.id          = e.user_id
+WHERE sr.shift_revenue > ma.avg_revenue_per_shift
+ORDER BY period DESC, sr.shift_revenue DESC, sr.shift_date DESC;
+}}}
+
+** Monthly revenue vs labor cost **
+{{{
+WITH monthly_revenue AS (
+    SELECT
+        DATE_TRUNC('month', o.datetime) as operation_month,
+        SUM(oi.quantity * oi.price) as revenue
     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;
+             JOIN order_items oi ON o.id = oi.order_id
+    GROUP BY DATE_TRUNC('month', o.datetime)
+),
+     monthly_labor_cost AS (
+         SELECT
+             monthly_assignments.operation_month,
+             SUM(e.gross_salary) as labor_cost
+         FROM (
+                  SELECT DISTINCT
+                      DATE_TRUNC('month', s.date) as operation_month,
+                      a.employee_id
+                  FROM shifts s
+                           JOIN assignments a ON s.id = a.shift_id
+              ) as monthly_assignments
+                  JOIN employees e ON monthly_assignments.employee_id = e.user_id
+         GROUP BY monthly_assignments.operation_month
+     )
+SELECT
+    TO_CHAR(COALESCE(mr.operation_month, mlc.operation_month), 'YYYY-MM') as period,
+    ROUND(COALESCE(mr.revenue, 0)::numeric, 2) as total_revenue,
+    ROUND(COALESCE(mlc.labor_cost, 0)::numeric, 2) as total_labor_cost,
+    ROUND(
+            CASE
+                WHEN COALESCE(mr.revenue, 0) > 0
+                    THEN (COALESCE(mlc.labor_cost, 0) / mr.revenue * 100)
+                ELSE 0
+                END::numeric, 2
+        ) as labor_as_percent_of_revenue
+FROM monthly_revenue mr
+         FULL OUTER JOIN monthly_labor_cost mlc ON mr.operation_month = mlc.operation_month
+ORDER BY period DESC;
+}}}
+
+** Server performance & revenue ranking **
+{{{
+WITH server_metrics AS (
+    SELECT
+        fs.employee_id,
+        u.email as server_email,
+        COUNT(DISTINCT a.id) as total_assignments,
+        COUNT(DISTINCT o.id) as orders_processed,
+        COALESCE(SUM(oi.quantity * oi.price), 0) as total_revenue_generated
+    FROM front_staff fs
+             JOIN employees e ON fs.employee_id = e.user_id
+             JOIN users u ON e.user_id = u.id
+             JOIN staff_roles sr ON fs.staff_role_id = sr.id
+             LEFT JOIN assignments a ON fs.employee_id = a.employee_id
+             LEFT JOIN shifts s ON a.shift_id = s.id
+             LEFT JOIN tab_orders to2 ON to2.front_staff_id = fs.employee_id
+             LEFT JOIN orders o ON o.id = to2.order_id
+        AND o.datetime >= CURRENT_DATE - INTERVAL '3 months'
+    LEFT JOIN order_items oi ON o.id = oi.order_id
+WHERE LOWER(sr.name) = 'server'
+GROUP BY fs.employee_id, u.email, u.phone_number,
+    e.net_salary, e.gross_salary, fs.tip_percent, sr.name
+    ),
+    performance_ranking AS (
+SELECT *,
+    RANK() OVER (ORDER BY total_revenue_generated DESC) as revenue_rank,
+    RANK() OVER (ORDER BY orders_processed DESC) as orders_rank,
+    CASE
+    WHEN total_assignments > 0
+    THEN (orders_processed::float / total_assignments)
+    ELSE 0
+    END as orders_per_assignment,
+    CASE
+    WHEN orders_processed > 0
+    THEN total_revenue_generated / orders_processed
+    ELSE 0
+    END as avg_revenue_per_order
+FROM server_metrics
+    )
+SELECT
+    server_email,
+    total_assignments,
+    orders_processed,
+    total_revenue_generated,
+    revenue_rank,
+    orders_rank,
+    ROUND(orders_per_assignment::numeric, 2) as avg_orders_per_shift,
+    ROUND(avg_revenue_per_order::numeric, 2) as avg_order_value
+FROM performance_ranking
+ORDER BY total_revenue_generated DESC, orders_processed DESC;
 }}}