Problem

Table: Purchases

+---------------+------+
| Column Name   | Type |
+---------------+------+
| user_id       | int  |
| purchase_date | date |
| amount_spend  | int  |
+---------------+------+
(user_id, purchase_date, amount_spend) is the primary key (combination of columns with unique values) for this table.
purchase_date will range from November 1, 2023, to November 30, 2023, inclusive of both dates.
Each row contains user id, purchase date, and amount spend.

Write a solution to calculate the total spending by users on each Friday of every week in November 2023. Output only weeks that include at least one purchase on a Friday.

Return the result table ordered by week of month inascending **** order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Input: 
Purchases table:
+---------+---------------+--------------+
| 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        |
+---------+---------------+--------------+
Output: 
+---------------+---------------+--------------+
| week_of_month | purchase_date | total_amount |
+---------------+---------------+--------------+
| 1             | 2023-11-03    | 5117         |
| 4             | 2023-11-24    | 21692        |
+---------------+---------------+--------------+ 
Explanation: 
- During the first week of November 2023, transactions amounting to $5,117 occurred on Friday, 2023-11-03.
- For the second week of November 2023, there were no transactions on Friday, 2023-11-10.
- Similarly, during the third week of November 2023, there were no transactions on Friday, 2023-11-17.
- In the fourth week of November 2023, two transactions took place on Friday, 2023-11-24, amounting to $12,000 and $9,692 respectively, summing up to a total of $21,692.
Output table is ordered by week_of_month in ascending order.

Solution

Method 1 – Group By Friday and Week of Month

Intuition

We need to sum the total spending for each Friday in November 2023, grouped by the week of the month. Only weeks with at least one Friday purchase should be included. We can use date functions to identify Fridays and their week number, then aggregate the spending.

Approach

  1. Identify all Fridays in November 2023 (Nov 3, 10, 17, 24).
  2. For each purchase, check if the purchase date is a Friday in November 2023.
  3. Assign a week number to each Friday (1 for Nov 3, 2 for Nov 10, etc.).
  4. Group by week number and sum the amount spent.
  5. Return only weeks with at least one Friday purchase, ordered by week number ascending.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

WITH fridays AS (
  SELECT '2023-11-03' AS friday, 1 AS week_of_month UNION ALL
  SELECT '2023-11-10', 2 UNION ALL
  SELECT '2023-11-17', 3 UNION ALL
  SELECT '2023-11-24', 4
)
SELECT f.week_of_month, SUM(p.amount_spend) AS total_amount
FROM fridays f
JOIN Purchases p ON p.purchase_date = f.friday
GROUP BY f.week_of_month
ORDER BY f.week_of_month;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

WITH fridays AS (
  SELECT '2023-11-03'::date AS friday, 1 AS week_of_month UNION ALL
  SELECT '2023-11-10', 2 UNION ALL
  SELECT '2023-11-17', 3 UNION ALL
  SELECT '2023-11-24', 4
)
SELECT f.week_of_month, SUM(p.amount_spend) AS total_amount
FROM fridays f
JOIN Purchases p ON p.purchase_date = f.friday
GROUP BY f.week_of_month
ORDER BY f.week_of_month;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

class Solution:
    def friday_purchases_i(self, purchases: 'pd.DataFrame') -> 'pd.DataFrame':
        import pandas as pd
        fridays = pd.to_datetime(['2023-11-03', '2023-11-10', '2023-11-17', '2023-11-24'])
        week_map = {pd.Timestamp('2023-11-03'): 1, pd.Timestamp('2023-11-10'): 2, pd.Timestamp('2023-11-17'): 3, pd.Timestamp('2023-11-24'): 4}
        df = purchases[purchases['purchase_date'].isin(fridays)].copy()
        df['week_of_month'] = df['purchase_date'].map(week_map)
        result = df.groupby('week_of_month', as_index=False)['amount_spend'].sum()
        result = result.rename(columns={'amount_spend': 'total_amount'})
        return result.sort_values('week_of_month').reset_index(drop=True)

Complexity

  • ⏰ Time complexity: O(n), where n is the number of purchase records, since each record is processed once.
  • 🧺 Space complexity: O(n), for storing filtered and grouped data.