Friday Purchases I

Friday Purchases I

What is this problem about?

The Friday Purchases I coding problem is a SQL task where you are given a table of purchases containing a purchase date and an amount. You need to calculate the total amount spent on purchases made exactly on Fridays, grouped by each week of a specific month (e.g., November 2023). The output should list the week number and the total Friday spending for that week.

Why is this asked in interviews?

Companies like TCS use this Database interview pattern to evaluate a candidate's proficiency with date and time functions in SQL. Extracting specific days of the week and calculating week numbers within a month are very common tasks in business reporting and ETL pipelines. It tests your ability to filter, group, and aggregate time-series data accurately.

Algorithmic pattern used

The problem is solved using Date Functions and Aggregation.

1. Filter: Use a WHERE clause to restrict the data to the specific month/year required.
2. Filter by Day: Use a function like DAYOFWEEK(), WEEKDAY(), or TO_CHAR() to keep only purchases made on a Friday.
3. Calculate Week: Determine the week of the month (often using CEIL(DAY(purchase_date) / 7) or similar DB-specific logic).
4. Aggregate: GROUP BY the calculated week number and SUM the purchase amounts.

Example explanation

Purchases in Nov 2023:

• 2023-11-03 (Friday, Week 1): $100
• 2023-11-04 (Saturday): $50 (Ignored)
• 2023-11-10 (Friday, Week 2): $200
• 2023-11-10 (Friday, Week 2): $50 Output: Week 1: 100 Week 2: 250 (200 + 50)

Common mistakes candidates make

• Dialect Differences: Using a date function from MySQL in a PostgreSQL environment (e.g., DAYOFWEEK vs EXTRACT(DOW FROM date)).
• Week Calculation: Assuming standard ISO week numbers (WEEK()) map perfectly to "Week 1, Week 2 of the month," which is usually not the case. Dividing the day of the month by 7 is usually required.
• Missing Weeks: If the problem requires showing weeks with 0 purchases, a simple GROUP BY will fail. You would need a LEFT JOIN against a static table of week numbers.

Interview preparation tip

Always clarify which SQL dialect (MySQL, Postgres, SQL Server) the interviewer expects, as date functions are the least standardized part of SQL. Practice extracting the day of the week and the week of the month.