Tasks Count in the Weekend - Problem
You're a project manager analyzing task submission patterns for your development team. Given a Tasks table that tracks when team members submit their completed tasks, you need to understand work-life balance patterns by counting how many tasks are submitted during weekends versus working days.
Table Structure:
| Column Name | Type |
|---|---|
| task_id | int |
| assignee_id | int |
| submit_date | date |
Where task_id is the primary key.
Your Goal: Write a SQL query to return exactly two numbers:
weekend_cnt- Tasks submitted on Saturday or Sundayworking_cnt- Tasks submitted on Monday through Friday
Example: If your team submitted 15 tasks during weekdays and 3 tasks during weekends, return weekend_cnt = 3, working_cnt = 15.
Input & Output
example_1.sql — Basic Weekend/Weekday Split
$
Input:
Tasks table:
+--------+------------+-------------+
| task_id| assignee_id| submit_date |
+--------+------------+-------------+
| 1 | 101 | 2022-06-13 |
| 2 | 102 | 2022-06-14 |
| 3 | 103 | 2022-06-15 |
| 4 | 104 | 2022-06-18 |
| 5 | 105 | 2022-06-19 |
+--------+------------+-------------+
›
Output:
+-------------+-------------+
| weekend_cnt | working_cnt |
+-------------+-------------+
| 1 | 4 |
+-------------+-------------+
💡 Note:
June 13, 2022 was a Monday (working), June 14 was Tuesday (working), June 15 was Wednesday (working), June 18 was Saturday (weekend), June 19 was Sunday (working - wait, that's Monday!). Actually: June 18 Saturday = weekend, others are weekdays, so 1 weekend task and 4 working tasks.
example_2.sql — All Weekend Tasks
$
Input:
Tasks table:
+--------+------------+-------------+
| task_id| assignee_id| submit_date |
+--------+------------+-------------+
| 1 | 201 | 2022-12-17 |
| 2 | 202 | 2022-12-18 |
| 3 | 203 | 2022-12-24 |
| 4 | 204 | 2022-12-25 |
+--------+------------+-------------+
›
Output:
+-------------+-------------+
| weekend_cnt | working_cnt |
+-------------+-------------+
| 4 | 0 |
+-------------+-------------+
💡 Note:
December 17, 2022 was Saturday, December 18 was Sunday, December 24 was Saturday, December 25 was Sunday. All 4 tasks were submitted during weekends, zero during working days.
example_3.sql — Single Task Edge Case
$
Input:
Tasks table:
+--------+------------+-------------+
| task_id| assignee_id| submit_date |
+--------+------------+-------------+
| 1 | 301 | 2022-08-15 |
+--------+------------+-------------+
›
Output:
+-------------+-------------+
| weekend_cnt | working_cnt |
+-------------+-------------+
| 0 | 1 |
+-------------+-------------+
💡 Note:
August 15, 2022 was a Monday (working day), so we have 0 weekend tasks and 1 working day task. Edge case with minimum possible dataset.
Constraints
- 1 ≤ number of tasks ≤ 104
- task_id is unique for each row
- submit_date is a valid date in YYYY-MM-DD format
- Saturday and Sunday are considered weekend days
- Monday through Friday are considered working days
Visualization
Tap to expand
Understanding the Visualization
1
Extract Day Info
Use date functions to determine what day of week each task was submitted
2
Categorize Days
Saturday (6/7) and Sunday (0/1) = Weekend, Monday-Friday = Working days
3
Conditional Counting
Use CASE statements to increment appropriate counters
4
Aggregate Results
SUM up all the conditional counts to get final weekend_cnt and working_cnt
Key Takeaway
🎯 Key Insight: Use database date functions to automatically categorize dates instead of manual checking, then leverage conditional aggregation to count each category in a single efficient query.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code