Tasks Count in the Weekend - Problem
You are given a Tasks table with information about task submissions.
Write a SQL query to report:
- The number of tasks submitted during the weekend (Saturday, Sunday) as
weekend_cnt - The number of tasks submitted during the working days (Monday-Friday) as
working_cnt
Return the result in any order.
Table Schema
Tasks
| Column Name | Type | Description |
|---|---|---|
task_id
PK
|
int | Primary key, unique task identifier |
assignee_id
|
int | ID of the person assigned to the task |
submit_date
|
date | Date when the task was submitted |
Primary Key: task_id
Note: Each row represents a task submission with its assignee and submission date
Input & Output
Example 1 — Mixed Weekend and Working Day Submissions
Input Table:
| task_id | assignee_id | submit_date |
|---|---|---|
| 1 | 1 | 2022-06-04 |
| 2 | 1 | 2022-06-06 |
| 3 | 2 | 2022-06-05 |
Output:
| weekend_cnt | working_cnt |
|---|---|
| 2 | 1 |
💡 Note:
Task 1 submitted on 2022-06-04 (Saturday), Task 2 on 2022-06-06 (Monday), and Task 3 on 2022-06-05 (Sunday). So we have 2 weekend submissions and 1 working day submission.
Example 2 — All Working Days
Input Table:
| task_id | assignee_id | submit_date |
|---|---|---|
| 4 | 3 | 2022-06-07 |
| 5 | 4 | 2022-06-08 |
| 6 | 5 | 2022-06-09 |
Output:
| weekend_cnt | working_cnt |
|---|---|
| 0 | 3 |
💡 Note:
All tasks were submitted on working days: Tuesday, Wednesday, and Thursday. Result shows 0 weekend submissions and 3 working day submissions.
Example 3 — All Weekend Submissions
Input Table:
| task_id | assignee_id | submit_date |
|---|---|---|
| 7 | 6 | 2022-06-11 |
| 8 | 7 | 2022-06-12 |
Output:
| weekend_cnt | working_cnt |
|---|---|
| 2 | 0 |
💡 Note:
Both tasks were submitted on weekend days: Saturday and Sunday. Result shows 2 weekend submissions and 0 working day submissions.
Constraints
-
1 ≤ task_id ≤ 10000 -
1 ≤ assignee_id ≤ 1000 -
submit_dateis a valid date
Visualization
Tap to expand
Understanding the Visualization
1
Extract DOW
Get day of week from submit_date
2
Classify
Weekend (0,6) vs Working (1-5)
3
Count
Sum each category
Key Takeaway
🎯 Key Insight: Use EXTRACT(DOW) to classify dates and conditional aggregation to count different day types in a single query
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code