Changes between Version 13 and Version 14 of AnalyticalStatisticalQuerying

Timestamp:

09/30/25 20:42:24 (3 months ago)

Author:

222077

Comment:

--

AnalyticalStatisticalQuerying

@@ -168 +168 @@
 
 }}}
-
-
 
 == 6. Top selling routes for transport organizers ==
@@ -210 +208 @@
 = Relational Algebra =
 
+== 3. Top user-purchased routes ==
+
+==== Original Query
+{{{
+WITH route_weighted_usage AS (
+    SELECT 
+        r.route_id,
+        l_from.name AS from_location,
+        l_to.name AS to_location,
+        to_.company_name AS transport_company,
+        SUM(
+            CASE 
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE) 
+                    THEN 0.5
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') 
+                    THEN 0.3
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months') 
+                    THEN 0.2
+                ELSE 0
+            END
+        ) AS weighted_score
+    FROM ticket t
+    JOIN trip tr ON t.trip_id = tr.trip_id
+    JOIN route r ON tr.route_id = r.route_id
+    JOIN location l_from ON r.from_location_id = l_from.location_id
+    JOIN location l_to ON r.to_location_id = l_to.location_id
+    JOIN transport_organizer to_ ON r.transport_organizer_id = to_.transport_organizer_id
+    WHERE t.account_id = 300 AND t.date_purchased >= CURRENT_DATE - INTERVAL '3 months'
+    GROUP BY r.route_id, l_from.name, l_to.name, to_.company_name
+)
+SELECT 
+    route_id,
+    from_location,
+    to_location,
+    transport_company,
+    weighted_score
+FROM route_weighted_usage
+WHERE weighted_score >= (SELECT AVG(weighted_score) FROM route_weighted_usage)
+ORDER BY weighted_score DESC;
+}}}
+
+== Results (Relational Algebra) ==
+{{{
+T1 = σ(account_id = 300 ∧ date_purchased ≥ CURRENT_DATE - 3m)(ticket)
+J1 = T1 ⨝ trip
+J2 = J1 ⨝ route
+J3 = J2 ⨝ l_from
+J4 = J3 ⨝ l_to
+J5 = J4 ⨝ transport_organizer
+
+P1 = π(route_id, l_from.name, l_to.name, to_.company_name, weight)(
+       extended projection with CASE(date_purchased) → weight
+     )(J5)
+
+G1 = γ(route_id, from_location, to_location, transport_company;
+        SUM(weight) → weighted_score)(P1)
+
+A1 = γ(; AVG(weighted_score) → avg_score)(G1)
+
+Result = τ_desc(weighted_score)(σ(weighted_score ≥ avg_score)(G1))
+}}}
+
+== Analysis (Optimizations) ==
+ * Push selection down: filter `ticket` by `account_id` and `date_purchased` before joins to reduce data early.
+ * Compute `weight` in a projection step before aggregation for clarity and efficiency.
+ * Replace correlated subquery for `AVG` with a window function or single aggregation to avoid repeated scans.
+ * Indexing opportunities:
+   - `ticket(account_id, date_purchased)` for fast filtering.
+   - Foreign key indexes on `ticket.trip_id`, `trip.route_id`, and `route.{from_location_id, to_location_id, transport_organizer_id}`.
+
+== Conclusion (Rewritten SQL) ==
+{{{
+WITH route_scores AS (
+    SELECT 
+        r.route_id,
+        l_from.name AS from_location,
+        l_to.name   AS to_location,
+        to_.company_name AS transport_company,
+        SUM(
+            CASE 
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE) 
+                    THEN 0.5
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') 
+                    THEN 0.3
+                WHEN DATE_TRUNC('month', t.date_purchased) = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '2 months') 
+                    THEN 0.2
+                ELSE 0
+            END
+        ) AS weighted_score
+    FROM ticket t
+    JOIN trip tr ON t.trip_id = tr.trip_id
+    JOIN route r ON tr.route_id = r.route_id
+    JOIN location l_from ON r.from_location_id = l_from.location_id
+    JOIN location l_to   ON r.to_location_id = l_to.location_id
+    JOIN transport_organizer to_ ON r.transport_organizer_id = to_.transport_organizer_id
+    WHERE t.account_id = 300 
+      AND t.date_purchased >= CURRENT_DATE - INTERVAL '3 months'
+    GROUP BY r.route_id, l_from.name, l_to.name, to_.company_name
+)
+SELECT *
+FROM (
+    SELECT route_scores.*, AVG(weighted_score) OVER () AS avg_score
+    FROM route_scores
+) ranked
+WHERE weighted_score >= avg_score
+ORDER BY weighted_score DESC;
+}}}
+
+
+
+== 5. Most popular destinations in the past year ==
+
+==== Original Query
+{{{
+WITH destinations AS (
+    SELECT 
+        l.name AS destination,
+        COUNT(t.ticket_id) AS total_passengers,
+        COUNT(DISTINCT t.account_id) AS unique_customers,
+        SUM(t.price) AS total_revenue,
+        COUNT(st.student_ticket_id) AS student_tickets,
+        COUNT(ct.child_ticket_id) AS child_tickets,
+        l.latitude,
+        l.longitude
+    FROM location l
+    JOIN ticket t ON l.location_id = t.gets_off_location_id
+    JOIN trip tr ON t.trip_id = tr.trip_id
+    JOIN transport_organizer to_org ON tr.transport_organizer_id = to_org.transport_organizer_id
+    LEFT JOIN student_ticket st ON t.ticket_id = st.ticket_id
+    LEFT JOIN child_ticket ct ON t.ticket_id = ct.ticket_id
+    GROUP BY l.location_id, l.name, l.latitude, l.longitude
+),
+stats AS (
+    SELECT AVG(total_passengers) AS mean_passengers
+    FROM destinations
+)
+SELECT 
+    d.destination,
+    d.total_passengers,
+    d.unique_customers,
+    d.total_revenue,
+    d.student_tickets,
+    d.child_tickets
+FROM destinations d, stats
+WHERE d.total_passengers >= stats.mean_passengers
+ORDER BY d.total_passengers DESC;
+}}}
+
+== Results (Relational Algebra) ==
+{{{
+J1 = location ⨝ ticket (l.location_id = t.gets_off_location_id)
+J2 = J1 ⨝ trip
+J3 = J2 ⨝ transport_organizer
+J4 = J3 ⟕ student_ticket
+J5 = J4 ⟕ child_ticket
+
+G1 = γ(l.location_id, l.name, l.latitude, l.longitude;
+        COUNT(ticket_id) → total_passengers,
+        COUNT_DISTINCT(account_id) → unique_customers,
+        SUM(price) → total_revenue,
+        COUNT(student_ticket_id) → student_tickets,
+        COUNT(child_ticket_id) → child_tickets)(J5)
+
+A1 = γ(; AVG(total_passengers) → mean_passengers)(G1)
+
+Result = τ_desc(total_passengers)(σ(total_passengers ≥ mean_passengers)(G1))
+}}}
+
+== Analysis (Optimizations) ==
+ * Push projections early: keep only `location_id`, `ticket_id`, `account_id`, `price`, and join keys before grouping.
+ * Apply the date filter (`past year`) explicitly in the `WHERE` clause on `ticket.date_purchased` (missing in original).
+ * Replace cross join with `stats` by using a window function (`AVG() OVER ()`) to avoid scanning `destinations` twice.
+ * Indexing opportunities:
+   - `ticket(gets_off_location_id, date_purchased)`
+   - `student_ticket.ticket_id`, `child_ticket.ticket_id`
+   - Foreign keys on `trip.route_id`, `trip.transport_organizer_id`.
+
+== Conclusion (Rewritten SQL) ==
+{{{
+WITH destinations AS (
+    SELECT 
+        l.name AS destination,
+        COUNT(t.ticket_id) AS total_passengers,
+        COUNT(DISTINCT t.account_id) AS unique_customers,
+        SUM(t.price) AS total_revenue,
+        COUNT(st.student_ticket_id) AS student_tickets,
+        COUNT(ct.child_ticket_id) AS child_tickets,
+        l.latitude,
+        l.longitude
+    FROM location l
+    JOIN ticket t ON l.location_id = t.gets_off_location_id
+    JOIN trip tr ON t.trip_id = tr.trip_id
+    JOIN transport_organizer to_org ON tr.transport_organizer_id = to_org.transport_organizer_id
+    LEFT JOIN student_ticket st ON t.ticket_id = st.ticket_id
+    LEFT JOIN child_ticket ct ON t.ticket_id = ct.ticket_id
+    WHERE t.date_purchased >= CURRENT_DATE - INTERVAL '1 year'
+    GROUP BY l.location_id, l.name, l.latitude, l.longitude
+)
+SELECT *
+FROM (
+    SELECT d.*,
+           AVG(total_passengers) OVER () AS mean_passengers
+    FROM destinations d
+) ranked
+WHERE total_passengers >= mean_passengers
+ORDER BY total_passengers DESC;
+}}}
 
 == 6. Top selling routes for transport organizers ==
@@ -293 +498 @@
 ORDER BY COUNT(tk.ticket_id) DESC;
 }}}
-