leecode - 1435. Create a Session Bar Chart

난이도 : Easy

 

Table: Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id is the primary key for this table.
duration is the time in seconds that a user has visited the application.

 

You want to know how long a user visits your application. You decided to create bins of "[0-5>", "[5-10>", "[10-15>", and "15 minutes or more" and count the number of sessions on it.

Write an SQL query to report the (bin, total).

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Sessions table:
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+
Output: 
+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+
Explanation: 
For session_id 1, 2, and 3 have a duration greater or equal than 0 minutes and less than 5 minutes.
For session_id 4 has a duration greater or equal than 5 minutes and less than 10 minutes.
There is no session with a duration greater than or equal to 10 minutes and less than 15 minutes.
For session_id 5 has a duration greater than or equal to 15 minutes.

 

 

WITH sub AS (
             SELECT '[0-5>' bin, 0 AS min_duration, 5 * 60 AS max_duration
             UNION ALL
             SELECT '[5-10>' bin, 5 * 60 AS min_duration, 10 * 60 AS max_duration
             UNION ALL
             SELECT '[10-15>' bin, 10 * 60 AS min_duration, 15 * 60 AS max_duration
             UNION ALL
             SELECT '15 or more' bin, 15 * 60 AS min_duration, 86400 AS max_duration
             )
SELECT sub.bin
     , COUNT(session_id) total
FROM sub
     LEFT JOIN Sessions ON Sessions.duration >= min_duration
                        AND Sessions.duration < max_duration
GROUP BY bin

Accepted (37.71%)

 

 

WITH sub AS (
            SELECT '[0-5>' bin, SUM(CASE WHEN duration >= 0 and duration <= 5 * 60 then 1 else 0 end) total FROM Sessions
            UNION ALL
            SELECT '[5-10>' bin, SUM(CASE WHEN duration >= 5 * 60 and duration <= 10 * 60 then 1 else 0 end) total FROM Sessions
            UNION ALL
            SELECT '[10-15>' bin, SUM(CASE WHEN duration >= 10 * 60 and duration <= 15 * 60 then 1 else 0 end) total FROM Sessions
            UNION ALL
            SELECT '15 or more' bin, SUM(CASE WHEN duration >= 15 * 60 then 1 else 0 end) total FROM Sessions
            )

SELECT bin
     , total
FROM sub

Accepted (5.97%)