Friday Purchases I - Problem

You are given a table Purchases containing user purchase data for November 2023.

Write a solution to calculate the total spending by users on each Friday of every week in November 2023.

Requirements:

  • Output only weeks that include at least one purchase on a Friday
  • Return results ordered by week of month in ascending order
  • Show week number and total Friday spending

Table Schema

Purchases
Column Name Type Description
user_id PK int User identifier
purchase_date PK date Date of purchase (November 1-30, 2023)
amount_spend PK int Amount spent on purchase
Primary Key: (user_id, purchase_date, amount_spend)
Note: Each row represents a unique purchase transaction

Input & Output

Example 1 — Multiple Friday Purchases
Input Table:
user_id purchase_date amount_spend
13 2023-11-03 20
10 2023-11-10 30
17 2023-11-17 40
12 2023-11-01 50
15 2023-11-24 80
Output:
week_of_month total_amount
1 20
2 30
3 40
4 80
💡 Note:

November 2023 Fridays are: 3rd (week 1), 10th (week 2), 17th (week 3), 24th (week 4). Each Friday's purchases are summed by week. Non-Friday purchases (like Nov 1st) are excluded.
Example 2 — Some Weeks Without Friday Purchases
Input Table:
user_id purchase_date amount_spend
11 2023-11-03 100
13 2023-11-03 200
12 2023-11-17 50
14 2023-11-02 75
15 2023-11-15 25
Output:
week_of_month total_amount
1 300
3 50
💡 Note:

Only weeks 1 and 3 have Friday purchases. Week 1 has two Friday purchases totaling 300 (100+200), week 3 has one purchase of 50. Weeks 2 and 4 have no Friday purchases so are excluded from output.

Constraints

  • purchase_date ranges from November 1, 2023, to November 30, 2023
  • 1 ≤ user_id ≤ 1000
  • 1 ≤ amount_spend ≤ 1000

Visualization

Tap to expand
Friday Purchases I - SQL Solution INPUT Purchases Table id user_id date amount 1 101 2023-11-03 50.00 2 102 2023-11-03 75.00 3 101 2023-11-04 30.00 4 103 2023-11-10 100.00 5 101 2023-11-17 200.00 6 104 2023-11-24 150.00 Friday dates Non-Friday November 2023 Fridays: 3, 10, 17, 24 3 10 17 24 ... Week 1, 2, 3, 4 ALGORITHM STEPS 1 Filter Fridays DAYOFWEEK(date) = 6 or DAYNAME = 'Friday' 2 Filter Nov 2023 YEAR = 2023 AND MONTH = 11 3 Get Week Number WEEK(date) or CEIL(DAY(date)/7) 4 Group and Sum GROUP BY week_of_month SUM(amount) SELECT week_of_month, SUM(amount) AS total FROM Purchases WHERE Friday filters... GROUP BY week ORDER BY week FINAL RESULT Output Table week_of_month total 1 125.00 2 100.00 3 200.00 4 150.00 Calculation Breakdown Week 1 (Nov 3): 50 + 75 = 125 Week 2 (Nov 10): 100 = 100 Week 3 (Nov 17): 200 = 200 Week 4 (Nov 24): 150 = 150 Ordered by week_of_month OK Key Insight: Use date functions to identify Fridays: DAYOFWEEK() returns 6 for Friday (MySQL) or use DAYNAME() = 'Friday'. Calculate week_of_month using CEIL(DAY(date)/7) or extract from WEEK() function. Only include weeks with actual Friday purchases. TutorialsPoint - Friday Purchases I | Optimal Solution Time: O(n) Space: O(weeks)

Related Problems

Asked in
Amazon 15 Microsoft 12
8.5K Views
Medium Frequency
~12 min Avg. Time
245 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen