Watch 10 video solutions for Friday Purchase III , a medium level problem involving Database. This walkthrough by NeetCode has 848,276 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.