Changes between Version 15 and Version 16 of AnalyticalStatisticalQuerying

Timestamp:

09/30/25 21:43:55 (13 days ago)

Author:

222077

Comment:

--

AnalyticalStatisticalQuerying

@@ -271 +271 @@
 
 === 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:
+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}`.
@@ -377 +377 @@
 
 === 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:
+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`
@@ -470 +470 @@
 
 === Analysis (Optimizations) ===
- * Push projections early to reduce carried attributes before aggregation.
- * Join order: start with fact tables (`ticket`, `payment`) then join dimension tables (`trip`, `route`, `location`, `transport_organizer`).
- * The `max_tickets` CTE is redundant unless we need to filter only top seller(s).
- * Indexing opportunities:
+Push projections early to reduce carried attributes before aggregation.
+Join order: start with fact tables (`ticket`, `payment`) then join dimension tables (`trip`, `route`, `location`, `transport_organizer`).
+The `max_tickets` CTE is redundant unless we need to filter only top seller(s).
+Indexing opportunities:
    - `ticket.trip_id`, `trip.route_id`, `ticket.payment_id`
    - Foreign keys: `route.from_location_id`, `route.to_location_id`, `route.transport_organizer_id`