Changes between Version 6 and Version 7 of AnalyticalStatisticalQuerying
- Timestamp:
- 09/19/25 02:08:03 (7 hours ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AnalyticalStatisticalQuerying
v6 v7 8 8 COUNT(DISTINCT o.id) as total_orders, 9 9 COUNT(DISTINCT r.user_id) as unique_customers, 10 COUNT(DISTINCT fs.employee_id) as active_employees,10 COUNT(DISTINCT a.employee_id) as active_employees, 11 11 COALESCE(SUM(oi.quantity * oi.price), 0) as daily_revenue 12 12 FROM generate_series( … … 15 15 '1 day'::interval 16 16 ) dates(operation_date) 17 LEFT JOIN reservations r 18 ON DATE(r.datetime) = dates.operation_date 19 LEFT JOIN orders o 20 ON DATE(o.datetime) = dates.operation_date 21 LEFT JOIN order_items oi 22 ON o.id = oi.order_id 23 LEFT JOIN tab_orders to2 24 ON o.id = to2.order_id 25 LEFT JOIN front_staff fs 26 ON to2.front_staff_id = fs.employee_id 17 LEFT JOIN reservations r ON DATE(r.datetime) = dates.operation_date 18 LEFT JOIN orders o ON DATE(o.datetime) = dates.operation_date 19 LEFT JOIN order_items oi ON o.id = oi.order_id 20 LEFT JOIN shifts s ON DATE(s.date) = dates.operation_date 21 LEFT JOIN assignments a ON s.id = a.shift_id 27 22 GROUP BY dates.operation_date 28 23 ORDER BY dates.operation_date DESC; … … 41 36 JOIN online_orders oo ON o.id = oo.order_id 42 37 WHERE o.datetime::date BETWEEN p_start_date AND p_end_date 43 44 38 UNION ALL 45 46 39 SELECT 'Tab Orders'::text AS order_type, 47 40 COALESCE(SUM(p.amount), 0)::numeric(14,2) AS total_revenue … … 80 73 start_time::time AS start_t, 81 74 end_time::time AS end_t 82 FROM 83 shifts 75 FROM shifts 84 76 ) 85 77 SELECT 86 78 TO_CHAR(o.datetime, 'YYYY-MM') AS period, 87 79 dsp.start_t::text || '-' || dsp.end_t::text AS shift_period, 88 SUM(oi.price * oi.quantity) AS total_revenue80 SUM(oi.price * oi.quantity) AS total_revenue 89 81 FROM 90 82 orders o … … 111 103 a.employee_id AS manager_id 112 104 FROM assignments a 113 JOIN shifts s 105 JOIN shifts s ON s.id = a.shift_id 114 106 JOIN managers m ON m.employee_id = a.employee_id 115 107 WHERE s.date >= date_trunc('year', CURRENT_DATE)::date 116 AND s.date < 108 AND s.date < (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')::date 117 109 ), 118 110 shift_revenue AS ( … … 129 121 ON o.datetime::date = mws.date 130 122 AND o.datetime::time >= mws.start_time 131 AND o.datetime::time < 123 AND o.datetime::time < mws.end_time 132 124 LEFT JOIN order_items oi ON oi.order_id = o.id 133 125 GROUP BY … … 144 136 sr.shift_date, 145 137 sr.start_time AS shift_start_time, 146 sr.end_time 147 u.email 138 sr.end_time AS shift_end_time, 139 u.email AS manager_email, 148 140 sr.shift_revenue, 149 141 ma.avg_revenue_per_shift, … … 151 143 FROM shift_revenue sr 152 144 JOIN monthly_avg ma ON ma.month_start = sr.month_start 153 JOIN managers m 154 JOIN employees e ON e.user_id= m.employee_id155 JOIN users u ON u.id= e.user_id145 JOIN managers m ON m.employee_id = sr.manager_id 146 JOIN employees e ON e.user_id = m.employee_id 147 JOIN users u ON u.id = e.user_id 156 148 WHERE sr.shift_revenue > ma.avg_revenue_per_shift 157 149 ORDER BY period DESC, sr.shift_revenue DESC, sr.shift_date DESC; … … 213 205 LEFT JOIN assignments a ON fs.employee_id = a.employee_id 214 206 LEFT JOIN shifts s ON a.shift_id = s.id 215 LEFT JOIN tab_orders to2 ON to2.front_staff_id = fs.employee_id 216 LEFT JOIN orders o ON o.id = to2.order_id 207 LEFT JOIN orders o ON o.employee_id = fs.employee_id 217 208 AND o.datetime >= CURRENT_DATE - INTERVAL '3 months' 218 209 LEFT JOIN order_items oi ON o.id = oi.order_id
