Watch the video solution for Tasks Count in the Weekend, a medium level problem involving Database. This walkthrough by Everyday Data Science has 1,733 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |