LC 511 & LC 1141 & LC 1693 & LC 1729 & LC 1741 & LC 1890

SQL-Intro
SQL
Publish Date:   2022-03-17

The following intro-level SQL problems are about ‘Function (COUNT, MAX, MIN)’.

LeetCode 511

Game Play Analysis I (Easy) [link]

SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;

LeetCode 1141

User Activity for the Past 30 Days I (Easy) [link]

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date;

Note that the following code is wrong, because the dates after ‘2019-07-27’ are included which is not what we want.

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date) < 30
GROUP BY activity_date;

To correct the code above, the datediff is restricted to be positive.

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE DATEDIFF('2019-07-27', activity_date) < 30 AND DATEDIFF('2019-07-27', activity_date) >= 0
GROUP BY activity_date;

LeetCode 1693

Daily Leads and Partners (Easy) [link]

SELECT 
    date_id, 
    make_name, 
    COUNT(DISTINCT lead_id) AS unique_leads, 
    COUNT(DISTINCT partner_id) AS unique_partners
FROM DailySales
GROUP BY date_id, make_name;

LeetCode 1729

Find Followers Count (Easy) [link]

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id;

LeetCode 1741

Find Total Time Spent by Each Employee (Easy) [link]

SELECT event_day AS day, emp_id, 
    SUM(out_time - in_time) AS total_time
FROM Employees
GROUP BY emp_id, event_day;

LeetCode 1890

The Latest Login in 2020 (Easy) [link]

SELECT t.user_id, MAX(t.time_stamp) AS last_stamp
FROM(
    SELECT user_id, time_stamp
    FROM Logins
    WHERE LEFT(time_stamp,4) = '2020'
) AS t
GROUP BY t.user_id;

A simplified version

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE LEFT(time_stamp,4) = '2020'
GROUP BY user_id;

SELECT user_id, MAX(time_stamp) AS last_stamp
FROM Logins
WHERE YEAR(time_stamp) = '2020'
GROUP BY user_id;
SQL-Intro
  TOC