Help Needed with sorting/filtering data over Timeframe Based on Aggregated results
I'm working on a feature that involves querying daily event data and aggregating metrics over a specified timeframe (e.g., last 30 days or 90 days) for each event. Based on these aggregates, I need to filter and sort the events according to certain thresholds, such as total revenue or average price within the selected timeframe. Here’s a more detailed breakdown of my issue:
Data Structure:
The Events table has a relation to
Query for all events within a given timeframe (e.g., last 30 or 90 days).
For each event, I need to aggregate the
Apply filters on these aggregated values. For example:
Only retrieve events where the sum of
Similarly, apply filters for
Finally, sort the results based on one of these aggregate fields (e.g., sort by
Challanges im facing
Filtering on Aggregated Data: I need to filter events based on the sum or average of fields in EventData over the specified timeframe. For example, filtering events with a total revenue between 1000 and 5000 within the last 30 days.
Sorting: I want to sort the filtered results based on these aggregated values (e.g., total revenue or average ticket price).
Data Structure:
Events tableEventData tabletotalRevenuetotalTicketsSoldavgTicketPriceeventDateThe Events table has a relation to
EventDataEventDataQuery for all events within a given timeframe (e.g., last 30 or 90 days).
For each event, I need to aggregate the
totalRevenuetotalTicketsSold avgTicketPriceApply filters on these aggregated values. For example:
Only retrieve events where the sum of
totalRevenueSimilarly, apply filters for
totalTicketsSoldavgTicketPriceFinally, sort the results based on one of these aggregate fields (e.g., sort by
totalRevenueChallanges im facing
Filtering on Aggregated Data: I need to filter events based on the sum or average of fields in EventData over the specified timeframe. For example, filtering events with a total revenue between 1000 and 5000 within the last 30 days.
Sorting: I want to sort the filtered results based on these aggregated values (e.g., total revenue or average ticket price).
