Friday Purchases I - Solution & Explanation

Problem Overview: The database contains a Purchases table with purchase dates and amounts. You need to report the total purchase amount for every Friday in November 2023. The key tasks are filtering rows that fall on a Friday, restricting results to the November 2023 range, and aggregating the purchase totals for each date.

Approach 1: Filter by Friday Using Date Functions (O(n) time, O(1) space)

The direct solution uses MySQL date functions to detect Fridays and then aggregates purchase amounts. Functions like WEEKDAY() or DAYOFWEEK() allow you to determine the day of the week for each purchase_date. Filter rows where the weekday corresponds to Friday and restrict the range to November 2023 using MONTH(), YEAR(), or a date range filter such as BETWEEN '2023-11-01' AND '2023-11-30'. After filtering, group by the purchase date and compute the total with SUM(amount). This approach scans the table once, making it O(n) time where n is the number of purchase rows. Memory usage stays O(1) because aggregation happens within the query engine without additional structures.

This technique relies on SQL database operations like filtering and grouping. It is concise and performs well for large datasets because the database optimizer can use indexes on the date column if available.

Approach 2: Generate Fridays and Join Purchases (O(n + f) time, O(f) space)

If the requirement is to show every Friday in the month even when no purchases occurred, you first generate the list of Fridays in November 2023. This can be done using MySQL date arithmetic such as DATE_ADD() or a recursive sequence to produce all dates, then filtering to Fridays with WEEKDAY(date) = 4. Once the Friday list is generated, perform a LEFT JOIN with the Purchases table on the date field and aggregate using SUM(amount). Missing purchase rows produce NULL, which can be converted to 0 using COALESCE().

This pattern combines calendar generation with aggregation and is common in reporting queries. The complexity becomes O(n + f), where n is the number of purchase rows and f is the number of generated Friday dates (a very small constant for a single month). The approach demonstrates strong understanding of SQL SQL date handling and reporting logic.

Recommended for interviews: The date-function filtering approach is what interviewers typically expect. It shows you understand SQL aggregation, date filtering, and weekday extraction. If the problem requires displaying Fridays without purchases, generating a calendar table and using a LEFT JOIN demonstrates deeper knowledge of reporting queries and database design patterns.