SQL架构
Table: Tasks
+-------------+------+ | Column Name | Type | +-------------+------+ | task_id | int | | assignee_id | int | | submit_date | date | +-------------+------+ task_id is the primary key for this table. Each row in this table contains the ID of a task, the id of the assignee, and the submission date.
Write an SQL query to report:
- the number of the tasks that were submitted during the weekend (Saturday, Sunday) as
weekend_cnt
, and - the number of the tasks that were submitted during the working days as
working_cnt
.
Return the result table in any order.
The query 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.
# Write your MySQL query statement below
select
sum(if( cast(date_format(submit_date,'%w') as signed) = 0 or cast(date_format(submit_date,'%w') as signed) =6 ,1,0)) weekend_cnt,
sum(if( cast(date_format(submit_date,'%w') as signed) > 0 and cast(date_format(submit_date,'%w') as signed) <6 ,1,0)) working_cnt
from
Tasks
# Write your MySQL query statement below
select
sum(if( date_format(submit_date,'%w') = 0 or date_format(submit_date,'%w') = 6 ,1,0)) weekend_cnt,
sum(if( date_format(submit_date,'%w') > 0 and date_format(submit_date,'%w') < 6 ,1,0)) working_cnt
from
Tasks