Changes between Version 3 and Version 4 of AnalyticalStatisticalQuerying

Timestamp:

02/15/25 10:48:26 (7 days ago)

Author:

221164

Comment:

--

AnalyticalStatisticalQuerying

@@ -3 +3 @@
 **Daily Revenue**
 {{{
-SELECT 
-    DATE(timestamp) AS payment_date, 
-    SUM(amount) AS total_revenue
-FROM payments
-GROUP BY DATE(timestamp)
-ORDER BY payment_date;
+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**
+**Daily Order Count and Average Order Value Report**
 {{{
-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
-GROUP BY DATE(o.datetime)
-ORDER BY order_date;
+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');
+
 }}}
 
-**Order Distribution by Order Category**
+** Revenue Split: Online vs. Tab Orders **
 {{{
-SELECT
-    c.name AS category,
-    COUNT(oi.id) AS total_items_ordered
-FROM order_items oi
-JOIN products p ON oi.product_id = p.id
-JOIN categories c ON p.category_id = c.id
-GROUP BY c.name
-ORDER BY total_items_ordered DESC;
+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');
 }}}
 
@@ -47 +79 @@
 }}}
 
-**Online Orders vs. Tab Orders Count**
+**Reservations by Day of Week**
 {{{
-SELECT 'Online Orders' AS order_type, COUNT(*) AS total_orders
-FROM online_orders
-UNION ALL
-SELECT 'Tab Orders' AS order_type, COUNT(*) AS total_orders
-FROM tab_orders;
+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;
 }}}
 
@@ -82 +122 @@
 }}}
 
-**Orders Managed by Each Front Staff**
+** Yearly Revenue Report **
 {{{
-SELECT
-    fs.employee_id,
-    COUNT(tord.order_id) AS total_orders_managed
-FROM tab_orders tord
-JOIN front_staff fs ON tord.front_staff_id = fs.employee_id
-GROUP BY fs.employee_id
-ORDER BY total_orders_managed DESC;
+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;
 }}}
-
-**Average Tip Percentage per Staff Role**
-{{{
-SELECT
-    sr.name AS staff_role,
-    AVG(fs.tip_percent) AS avg_tip_percent
-FROM front_staff fs
-JOIN staff_roles sr ON fs.staff_role_id = sr.id
-GROUP BY sr.name;
-}}}
-
-**Inventory Turnover Ratio per Product**
-{{{
-SELECT
-    p.name AS product,
-    inv.quantity AS current_inventory,
-    SUM(oi.quantity) AS total_quantity_ordered,
-    CASE
-        WHEN inv.quantity = 0 THEN NULL
-        ELSE SUM(oi.quantity)::decimal / inv.quantity
-    END AS turnover_ratio
-FROM products p
-JOIN inventories inv ON p.id = inv.product_id
-JOIN order_items oi ON p.id = oi.product_id
-GROUP BY p.name, inv.quantity
-ORDER BY turnover_ratio DESC;
-}}}
-
-**Most Popular Table by Reservations**
-{{{
-SELECT
-    table_number,
-    COUNT(*) AS reservation_count
-FROM frontstaff_managed_reservations
-GROUP BY table_number
-ORDER BY reservation_count DESC
-LIMIT 1;
-}}}
-
-**Reservations by Day of Week**
-{{{
-SELECT
-    EXTRACT(DOW FROM datetime) AS day_of_week,  -- 0=Sunday, 1=Monday, etc.
-    COUNT(*) AS total_reservations
-FROM reservations
-GROUP BY day_of_week
-ORDER BY total_reservations DESC;
-}}}