Changes between Version 7 and Version 8 of AnalyticalStatisticalQuerying

Timestamp:

09/23/25 19:08:50 (45 hours ago)

Author:

226030

Comment:

--

AnalyticalStatisticalQuerying

@@ -22 +22 @@
 GROUP BY dates.operation_date
 ORDER BY dates.operation_date DESC;
+}}}
+
+Relational algebra:
+
+We will use:
+
+The generated series as given in our relational algebra and noted as D, Reservations as R, Orders as O, OrderItems as OI, Shifts as S and Assignments as A
+ 
+{{{
+operation_dateℑdates_.operation_date, total_reservations=COUNT(R.id),total_orders=COUNT(O.id),unique_costumers=COUNT(R.user_id), active_employees=COUNT(A.employee_id), daily_revenue=SUM(OI.quantity*OI.price) 
+
+(((((R ⟕ r.datetime = dates.operation_date D) ⟕ dates.operation_date=o.datetime O) ⟕ o.id=oi.order_id OI) ⟕ dates.operation_date=s.date S) ⟕ s.id=a.shift_id A)
+
 }}}
 
@@ -47 +60 @@
 }}}
 
+Relational algebra:
+
+We will use:
+
+OnlineOrders as OO, Payments as P, Orders as O and TabORders as TORD
+{{{
+π order_type=’OnlineOrders’,total_revenue=SUM(amount)(σ p_start_date<=o.o_timestamp<=p_end_date(OO⋈OO.order_id=O.id(P⋈P.order_id=O.id O))
+
+U
+
+π order_type=’TabOrders’,total_revenue=SUM(amount)(σ p_start_date<=o.o_timestamp<=p_end_date(TORD⋈TORD.order_id=O.order_id (P ⋈P.order_id=O.id O))
+}}}
+
 **Top 10 products by revenue **
 {{{
@@ -64 +90 @@
 ORDER BY total_revenue DESC
     LIMIT 10;
+}}}
+
+Relational algebra:
+
+We will use:
+
+Products as P, OrderItems as OI, Category as C and TabOrders as TORD
+{{{
+p.id,p.name,c.nameℑproduct_id=P.id,product_name=P.name,category_name=C.name,total_quantity=SUM(oi.quantity), revenue_share_percent=100.0 * SUM(OI.quantity * OI.price) / SUM(SUM(OI.quantity * OI.price))
+  
+(σ O.datetime>= current_date-90(((C ⋈P.category_id = c_id P) ⋈P.id=OI.product_id OI) ⋈o.id=oi.order_id O))
 }}}
 
@@ -93 +130 @@
 }}}
 
+Relational algebra:
+
+We refer Shift as S, OrderItems as OI, Orders as O
+{{{
+DSP = πstart_t=start_time,end_t=end_time(S)
+
+period, shift_periodℱtotal_revenue=SUM(OI.price*OI.quantity) 
+
+((O ⋈ O.id = OI.order_id OI) ⋈ O.datetime::time>DSP.start_t ⋀ O.datetime::time<DSP.end_t DSP))
+}}}
 ** Managers' shifts above monthly average revenue **
 {{{
@@ -148 +195 @@
 WHERE sr.shift_revenue > ma.avg_revenue_per_shift
 ORDER BY period DESC, sr.shift_revenue DESC, sr.shift_date DESC;
+}}}
+
+Relational algebra:
+
+We use manager_worked_shifts as MWS, shift_revenue as SR mothly_avg as MA, Shifts as S, Assignment as A, Managers as M, Employees as E, Users as U and end_date= (date_trunc('year', CURRENT_DATE) + INTERVAL '1 year')
+{{{
+MWS = π shift_id=S.id, S.date, S.start_time,S.end_time,manager_id=A.employee_id
+(σS.date>=date⋀ S.date<end_date)((A ⋈S.id=A.shift_id S) ⋈ A.employee_id= M.employee_id M))
+
+SR =mws.shift_id, month_start, mws.date, mws.start_time, mws.end_time, mws.manager_id ℱshift_revenue = SUM(oi.quantity * oi.price)
+(( MWS ⟕ o.datetime::date=mws.date ⋀ o.datetime::time >= mws.start_time ⋀ O.datetime::time < MWS.end_time O) ⟕ O.id=OI.id OI) 
+
+MA = month_start ℱ avg_revenue_per_shift= AVG(SR.shift_revenue)(SR)
+
+π period=sr.month_start,sr.shift_id,sr.shift_date, shift_start_time=sr.start_time, shift_end_time=sr.send_time, manager_email = u.email,sr.shift_revenue, ma.avg_revenue_per_shift, above_by = sr.shift_revenue - ma.avg_revenue_per_shift
+(σ sr.shift_revenue > ma.avg_revenue_per_shift((((SR ⋈SR.month_start=MA.month_start MA) ⋈ SR.manager_id=M.employee_id M) ⋈  m.employee_id=e.user_id E) ⋈ e.user_id=u.id U))
 }}}