Table: Tasks
+-------------+------+ | 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:
weekend_cnt, andworking_cnt.Return the result table in any order.
The result format is shown in the following example.
Example 1:
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.
Problem Overview: Given a Tasks table with a task submission date, compute how many tasks were created during the weekend. Weekend days are Saturday and Sunday, so the query needs to identify rows where the date falls on those two days and count them.
Approach 1: Filter with DAYOFWEEK() (O(n) time, O(1) space)
Scan the table once and use MySQL’s DAYOFWEEK(date) function to determine the weekday for each record. In MySQL, DAYOFWEEK() returns 1 for Sunday and 7 for Saturday. Filter rows where the result is either 1 or 7, then apply COUNT(*) to compute the total number of weekend tasks. This works because the database engine evaluates the function for each row and performs a simple conditional filter before aggregation.
The key insight is that you do not need joins, subqueries, or grouping. The database can evaluate the weekday directly from the date column and perform the aggregation in a single pass. The query effectively performs a linear scan of the table, which is optimal for this type of filtering problem.
An alternative variant uses WEEKDAY(date), which returns values from 0 (Monday) to 6 (Sunday). In that case, you filter for 5 and 6 to represent Saturday and Sunday. Both functions solve the same problem; the choice depends on which weekday numbering system you prefer.
This problem mainly tests your understanding of SQL date functions and conditional filtering rather than complex database operations. Recognizing how to extract weekday information from a date column is a common pattern in database queries, especially in analytics and reporting workloads.
Recommended for interviews: The DAYOFWEEK() filtering approach is the expected solution. It demonstrates practical knowledge of SQL date functions and efficient aggregation in relational databases. Simpler logic with a single scan shows stronger SQL fluency than overcomplicated queries.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Filter with DAYOFWEEK() | O(n) | O(1) | Standard MySQL solution when using Sunday=1 and Saturday=7 weekday numbering |
| Filter with WEEKDAY() | O(n) | O(1) | Preferred when using Monday=0 to Sunday=6 numbering for easier weekend checks |
LeetCode Medium 2298 "Tasks Count in the Weekend" Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,733 views views
Practice Tasks Count in the Weekend with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor