Friday Purchases II

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. If there are no purchases on a particular Friday of a week , it will be considered as 0.

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
28
29


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         |
| 2             | 2023-11-10    | 0            |
| 3             | 2023-11-17    | 0            |
| 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, resulting in a value of 0 in the output table for that day.
- Similarly, during the third week of November 2023, there were no transactions on Friday, 2023-11-17, reflected as 0 in the output table for that specific day.
- 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 – Generate All Fridays and Left Join for Zeroes

Intuition

To report total spending for each Friday in November 2023, including zero for Fridays with no purchases, we generate all Fridays and left join with the Purchases table. This ensures every Friday is present in the result, even if there are no purchases.

Approach

Generate a table of all Fridays in November 2023 (Nov 3, 10, 17, 24).
Left join this table with Purchases on purchase_date.
Group by week number and Friday date, summing amount_spend (using COALESCE to handle nulls as 0).
Output all Fridays, even if total_amount is 0, ordered by week_of_month 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, f.friday AS purchase_date, COALESCE(SUM(p.amount_spend), 0) AS total_amount
FROM fridays f
LEFT JOIN Purchases p ON p.purchase_date = f.friday
GROUP BY f.week_of_month, f.friday
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, f.friday AS purchase_date, COALESCE(SUM(p.amount_spend), 0) AS total_amount
FROM fridays f
LEFT JOIN Purchases p ON p.purchase_date = f.friday
GROUP BY f.week_of_month, f.friday
ORDER BY f.week_of_month;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


class Solution:
    def friday_purchases_ii(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', 'purchase_date'], as_index=False)['amount_spend'].sum()
        # Ensure all Fridays are present
        all_fridays = pd.DataFrame({'week_of_month': [1,2,3,4], 'purchase_date': fridays})
        result = all_fridays.merge(result, on=['week_of_month', 'purchase_date'], how='left').fillna(0)
        result['total_amount'] = result['amount_spend'].astype(int)
        result = result[['week_of_month', 'purchase_date', '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.

Problem#

Examples#

Solution#

Method 1 – Generate All Fridays and Left Join for Zeroes#

Intuition#

Approach#

Code#

Complexity#