Sven
Explore posts from serversPPrisma
•Created by Sven on 11/11/2024 in #help-and-questions
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:
Events table: Stores information about each event, like its name and category.
EventData table: Contains daily metrics for each event, including:
totalRevenue: Total revenue generated by the event on a given day.
totalTicketsSold: Total tickets sold on that day.
avgTicketPrice: Average ticket price for that day.
eventDate: The date of the event metrics.
The Events table has a relation to EventData, where EventData holds the daily details.
Query for all events within a given timeframe (e.g., last 30 or 90 days).
For each event, I need to aggregate the totalRevenue, totalTicketsSold, and avgTicketPriceover the timeframe.
Apply filters on these aggregated values. For example:
Only retrieve events where the sum of totalRevenue in the timeframe is between a specified minimum and maximum.
Similarly, apply filters for totalTicketsSold and avgTicketPrice (where avgticketprice is average price over that timeframe) based on specified thresholds.
Finally, sort the results based on one of these aggregate fields (e.g., sort by totalRevenuein descending order).
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).5 replies