Changes between Initial Version and Version 1 of AnalyticalStatisticalQuerying

Timestamp:

02/10/25 11:25:13 (12 days ago)

Author:

221164

Comment:

--

AnalyticalStatisticalQuerying

@@ +1 @@
+== Analytical and Statistical Queries
+
+**Daily Revenue**
+{{{
+SELECT 
+    DATE(timestamp) AS payment_date, 
+    SUM(amount) AS total_revenue
+FROM payments
+GROUP BY DATE(timestamp)
+ORDER BY payment_date;
+}}}
+
+**Daily Order Count and Average Order Value**
+{{{
+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;
+}}}
+
+**Order Distribution by Order Category**
+{{{
+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;
+}}}
+
+**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;
+}}}
+
+**Online Orders vs. Tab Orders Count**
+{{{
+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;
+}}}
+
+**Revenue Split: Online vs Tab Orders**
+{{{
+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;
+
+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;
+}}}
+
+**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;
+}}}
+
+**Orders Managed by Each Front Staff**
+{{{
+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;
+}}}
+
+**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;
+}}}