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.
Table: Users
+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id | int |
| membership | enum |
+-------------+------+
user_id is the primary key for this table.
membership is an ENUM (category) type of ('Standard', 'Premium', 'VIP').
Each row of this table indicates the user_id, membership type.
Write a solution to calculate the total spending by Premium and VIP members on each Friday of every week in November 2023. If there are no purchases on a particular Friday by Premium or VIP members, it should be considered as 0.
Return the result table ordered by week of the month, and membership in ascending order.
The result format is in the following example.
Example:
Input:
Purchases table:
+---------+---------------+--------------+ | user_id | purchase_date | amount_spend | +---------+---------------+--------------+ | 11 | 2023-11-03 | 1126 | | 15 | 2023-11-10 | 7473 | | 17 | 2023-11-17 | 2414 | | 12 | 2023-11-24 | 9692 | | 8 | 2023-11-24 | 5117 | | 1 | 2023-11-24 | 5241 | | 10 | 2023-11-22 | 8266 | | 13 | 2023-11-21 | 12000 | +---------+---------------+--------------+
Users table:
+---------+------------+ | user_id | membership | +---------+------------+ | 11 | Premium | | 15 | VIP | | 17 | Standard | | 12 | VIP | | 8 | Premium | | 1 | VIP | | 10 | Standard | | 13 | Premium | +---------+------------+
Output:
+---------------+-------------+--------------+
| week_of_month | membership | total_amount |
+---------------+-------------+--------------+
| 1 | Premium | 1126 |
| 1 | VIP | 0 |
| 2 | Premium | 0 |
| 2 | VIP | 7473 |
| 3 | Premium | 0 |
| 3 | VIP | 0 |
| 4 | Premium | 5117 |
| 4 | VIP | 14933 |
+---------------+-------------+--------------+
Explanation:
Note: The output table is ordered by week_of_month and membership in ascending order.
First, we create a recursive table T that includes a week_of_month column, representing the week of the month. Then we create a table M that includes a membership column, representing the type of membership, with values 'Premium' and 'VIP'.
Next, we create a table P that includes week_of_month, membership, and amount_spend columns, filtering out the amount spent by each member on Fridays of each week of the month. Finally, we join tables T and M, then left join table P, and group by week_of_month and membership columns to calculate the total spending of each type of member each week.
Sliding Window: Best Time to Buy and Sell Stock - Leetcode 121 - Python • NeetCode • 848,276 views views
Watch 9 more video solutions →Practice Friday Purchase III with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor