Problem

Table: Tasks

1
2
3
4
5
6
7
8
9

+-------------+------+
| Column Name | Type |
+-------------+------+
| task_id     | int  |
| assignee_id | int  |
| submit_date | date |
+-------------+------+
task_id is the primary key (column with unique values) for this table.
Each row in this table contains the ID of a task, the id of the assignee, and the submission date.

Write a solution to report:

  • the number of tasks that were submitted during the weekend (Saturday, Sunday) as weekend_cnt, and
  • the number of tasks that were submitted during the working days as working_cnt.

Return the result table in any order.

The result format is shown in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Input: 
Tasks table:
+---------+-------------+-------------+
| task_id | assignee_id | submit_date |
+---------+-------------+-------------+
| 1       | 1           | 2022-06-13  |
| 2       | 6           | 2022-06-14  |
| 3       | 6           | 2022-06-15  |
| 4       | 3           | 2022-06-18  |
| 5       | 5           | 2022-06-19  |
| 6       | 7           | 2022-06-19  |
+---------+-------------+-------------+
Output: 
+-------------+-------------+
| weekend_cnt | working_cnt |
+-------------+-------------+
| 3           | 3           |
+-------------+-------------+
Explanation: 
Task 1 was submitted on Monday.
Task 2 was submitted on Tuesday.
Task 3 was submitted on Wednesday.
Task 4 was submitted on Saturday.
Task 5 was submitted on Sunday.
Task 6 was submitted on Sunday.
3 tasks were submitted during the weekend.
3 tasks were submitted during the working days.

Solution

Method 1 - Weekend vs Working Day Count

Explanation

We use date functions to determine the day of the week for each submission. In MySQL, DAYOFWEEK returns 1 for Sunday and 7 for Saturday. In Oracle, TO_CHAR(..., 'D') returns the day number (may depend on NLS settings). In PostgreSQL, EXTRACT(ISODOW FROM ...) returns 6 for Saturday and 7 for Sunday.

Approach

We count the number of tasks submitted on weekends (Saturday, Sunday) and working days. We use the day-of-week function and CASE WHEN expression to distinguish between them and aggregate counts.

Complexity

  • ⏰ Time complexity: O(N) – We scan the Tasks table once to compute the aggregates.
  • 🧺 Space complexity: O(1) – Only constant extra aggregation state is used.

Code

1
2
3
4

SELECT
  SUM(CASE WHEN DAYOFWEEK(submit_date) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_cnt,
  SUM(CASE WHEN DAYOFWEEK(submit_date) NOT IN (1, 7) THEN 1 ELSE 0 END) AS working_cnt
FROM Tasks;
1
2
3
4

SELECT
  SUM(CASE WHEN TO_CHAR(submit_date, 'D') IN ('1', '7') THEN 1 ELSE 0 END) AS weekend_cnt,
  SUM(CASE WHEN TO_CHAR(submit_date, 'D') NOT IN ('1', '7') THEN 1 ELSE 0 END) AS working_cnt
FROM Tasks;
1
2
3
4

SELECT
  SUM(CASE WHEN EXTRACT(ISODOW FROM submit_date) IN (6, 7) THEN 1 ELSE 0 END) AS weekend_cnt,
  SUM(CASE WHEN EXTRACT(ISODOW FROM submit_date) NOT IN (6, 7) THEN 1 ELSE 0 END) AS working_cnt
FROM Tasks;