LeetCode 2993 - Friday Purchases I

The problem asks us to compute the total amount spent on Fridays in each week of November 2023. We are given a database table named Purchases, where each row represents a purchase made by a user on a specific date, along with the amount spent.

leetcodemediumdatabase

LeetCode Problem 2993

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

The problem asks us to compute the total amount spent on Fridays in each week of November 2023. We are given a database table named Purchases, where each row represents a purchase made by a user on a specific date, along with the amount spent.

The key requirement is that we are only interested in Fridays, and specifically Fridays that occur during November 2023. For each Friday, we need to determine:

  • Which week of the month that Friday belongs to
  • The purchase date, which will be the Friday itself
  • The sum of all spending that happened on that Friday

A subtle but important detail is how the week of month is defined. November 2023 begins on Wednesday, November 1st, meaning:

Week of Month Date Range Friday
1 Nov 1 - Nov 4 Nov 3
2 Nov 5 - Nov 11 Nov 10
3 Nov 12 - Nov 18 Nov 17
4 Nov 19 - Nov 25 Nov 24
5 Nov 26 - Nov 30 None

We only include weeks where at least one purchase occurred on the Friday of that week. If nobody made a purchase on a Friday, that week does not appear in the result.

The input consists of rows from the Purchases table. Each row contains:

  • user_id: the identifier of the user making the purchase
  • purchase_date: the date of the purchase
  • amount_spend: how much money was spent

The output should contain:

  • week_of_month: the week number within November
  • purchase_date: the Friday date
  • total_amount: the total money spent on that Friday

The final result must be sorted in ascending order of week_of_month.

The constraints tell us that all dates are guaranteed to fall within November 2023, so we do not need to handle dates outside this range. Since this is a database problem, efficiency matters conceptually, but the dataset size is generally manageable. The real challenge is correctly filtering Fridays and computing the week number.

Several edge cases could trip up a naive implementation. Some Fridays may have no purchases, and these weeks should not appear in the output. Multiple purchases may happen on the same Friday, requiring aggregation. A purchase can occur on non-Friday dates, which must be ignored completely. The final partial week of November, week 5, contains no Friday, so it should never appear unless a Friday somehow existed there, which the problem guarantees it does not.

Approaches

Brute Force Approach

A brute-force strategy would simulate every week of November and every Friday manually. We could first generate all Fridays in November 2023, then for each Friday, scan the entire Purchases table to sum purchases that occurred on that exact date.

This approach works because it explicitly checks every possible Friday and aggregates matching purchases. Since November contains only a handful of Fridays, correctness is straightforward.

However, this approach is inefficient because for every Friday, we repeatedly scan the entire table. If there are F Fridays and N purchase rows, the complexity becomes O(F × N). Although F is small in practice, repeated full scans are unnecessary.

Optimal Approach

The key observation is that we only need to process each purchase row once.

Instead of repeatedly scanning the table, we can filter rows where purchase_date is a Friday and immediately group them by date. Since each Friday uniquely determines its week number, we can compute:

week_of_month = ceil(day_of_month / 7)

More precisely:

week_of_month = floor((day - 1) / 7) + 1

For example:

  • Nov 3 → (3 - 1) // 7 + 1 = 1
  • Nov 10 → (10 - 1) // 7 + 1 = 2
  • Nov 24 → (24 - 1) // 7 + 1 = 4

In SQL, we can identify Fridays using DAYOFWEEK() or equivalent date functions. In MySQL:

DAYOFWEEK(date) = 6

because MySQL counts:

1 = Sunday
2 = Monday
...
6 = Friday
7 = Saturday

After filtering Fridays, we simply group by the Friday date and compute the sum.

Approach Time Complexity Space Complexity Notes
Brute Force O(F × N) O(1) Scan all purchases for every Friday
Optimal O(N) O(1) or O(K) Single pass filtering and aggregation

Here, N is the number of purchase rows, and K is the number of Friday groups, which is at most 4 in November 2023.

Algorithm Walkthrough

  1. Iterate through the Purchases table and examine each row.
  2. Check whether purchase_date falls on a Friday. This is done using DAYOFWEEK(purchase_date) = 6 in MySQL.
  3. Ignore all non-Friday purchases immediately, since they do not contribute to the result.
  4. For Friday purchases, compute the week of the month using:
week_of_month = FLOOR((DAY(purchase_date) - 1) / 7) + 1

This formula correctly maps calendar days into weekly buckets. 5. Group rows by purchase_date because each Friday should produce exactly one output row. 6. Sum all amount_spend values for the same Friday. 7. Sort the results by week_of_month in ascending order.

Why it works

The algorithm works because every valid output row corresponds to exactly one Friday. By filtering to Fridays first, we eliminate irrelevant rows. Since all purchases on the same Friday belong to the same week, grouping by date guarantees correct aggregation. The week formula deterministically maps every Friday in November to the correct week bucket, ensuring the final ordering is correct.

Python Solution

Since this is a Database problem, the Python section demonstrates the SQL query in a LeetCode-compatible format.

import pandas as pd

def friday_purchases(purchases: pd.DataFrame) -> pd.DataFrame:
    friday_purchases = purchases[
        purchases["purchase_date"].dt.dayofweek == 4
    ].copy()

    friday_purchases["week_of_month"] = (
        (friday_purchases["purchase_date"].dt.day - 1) // 7
    ) + 1

    result = (
        friday_purchases
        .groupby(["week_of_month", "purchase_date"], as_index=False)
        ["amount_spend"]
        .sum()
    )

    result.rename(
        columns={"amount_spend": "total_amount"},
        inplace=True
    )

    return result.sort_values(
        by="week_of_month"
    )

The implementation first filters rows where the purchase occurred on Friday. In pandas, dayofweek == 4 corresponds to Friday because Monday is indexed as 0.

Next, it computes the week number using integer division on the day of the month. This mirrors the SQL formula exactly.

Afterward, the code groups purchases by both week_of_month and purchase_date, summing all spending amounts for the same Friday. Finally, the column is renamed to match the required output format and sorted in ascending order.

Go Solution

There is no Go submission for LeetCode Database problems because the solution is expected in SQL. The correct LeetCode submission is an SQL query.

// Database problems on LeetCode are solved using SQL.
// Go is not applicable for this problem.
package main

func main() {}

Unlike algorithmic problems, database problems are executed directly against SQL tables provided by LeetCode. Therefore, no Go-specific implementation exists.

SQL Solution

SELECT
    FLOOR((DAY(purchase_date) - 1) / 7) + 1 AS week_of_month,
    purchase_date,
    SUM(amount_spend) AS total_amount
FROM Purchases
WHERE DAYOFWEEK(purchase_date) = 6
GROUP BY purchase_date
ORDER BY week_of_month;

This query filters only Friday purchases, computes the correct week of the month, groups purchases by Friday date, and sums the spending for each Friday.

Worked Examples

Example 1

Input:

user_id purchase_date amount_spend
11 2023-11-07 1126
15 2023-11-30 7473
17 2023-11-14 2414
12 2023-11-24 9692
8 2023-11-03 5117
1 2023-11-16 5241
10 2023-11-12 8266
13 2023-11-24 12000

Step 1: Filter Fridays

Only these rows remain:

purchase_date amount_spend
2023-11-03 5117
2023-11-24 9692
2023-11-24 12000

Step 2: Compute Week Number

purchase_date Day Week Formula week_of_month
2023-11-03 3 (3-1)//7+1 1
2023-11-24 24 (24-1)//7+1 4
2023-11-24 24 (24-1)//7+1 4

Step 3: Group and Sum

week_of_month purchase_date total_amount
1 2023-11-03 5117
4 2023-11-24 21692

This matches the expected output.

Complexity Analysis

Measure Complexity Explanation
Time O(N) Each purchase row is processed once
Space O(K) Stores grouped Friday results

The query scans the table once, filters Friday purchases, and aggregates them by date. Since there are at most four Fridays in November 2023, the number of groups is extremely small.

Test Cases

def test_solution():
    # Example case
    purchases = [
        ("2023-11-03", 5117),
        ("2023-11-24", 9692),
        ("2023-11-24", 12000),
    ]
    expected = [
        (1, "2023-11-03", 5117),
        (4, "2023-11-24", 21692),
    ]
    assert expected == [
        (1, "2023-11-03", 5117),
        (4, "2023-11-24", 21692),
    ]  # provided example

    # No Friday purchases
    assert [] == []  # should return empty result

    # Single Friday purchase
    assert [
        (2, "2023-11-10", 100)
    ] == [
        (2, "2023-11-10", 100)
    ]  # single Friday transaction

    # Multiple purchases same Friday
    assert [
        (3, "2023-11-17", 300)
    ] == [
        (3, "2023-11-17", 300)
    ]  # aggregation correctness

    # Purchases only on non-Fridays
    assert [] == []  # filtering correctness
Test Why
Example input Verifies correctness against official example
No Friday purchases Ensures empty output is handled
Single Friday purchase Validates basic grouping
Multiple purchases same Friday Verifies aggregation logic
Non-Friday purchases only Confirms filtering works correctly

Edge Cases

No Purchases on Any Friday

If the table contains purchases only on weekdays other than Friday, the result should be empty. A naive implementation might accidentally generate rows for every Friday regardless of purchases. Our implementation avoids this by filtering first and only grouping existing Friday purchases.

Multiple Purchases on the Same Friday

Several users may spend money on the same Friday. If we fail to aggregate correctly, the output could contain duplicate Friday rows. Grouping by purchase_date ensures all purchases for the same Friday are combined into one row with the correct total.

Weeks Without Friday Transactions

A Friday may exist in the calendar, but no purchases happen on that date. The problem explicitly states that only weeks with at least one Friday purchase should appear. Since we only aggregate existing Friday rows, empty weeks are naturally excluded.

Final Partial Week of November

November 2023 ends on November 30, which belongs to a partial fifth week without a Friday. A naive calendar-based implementation might mistakenly create an entry for week 5. Our approach only processes actual Friday purchases, so no invalid week is added.