As we know, the first 70% of SQL is pretty straightforward but the remaining 30% can be pretty tricky.
So, in this blog, some popular hard SQL interview questions will be covered for people to sharpen their skills.
Self-Join Practice Problems
Part 1: How much a key metric, e.g., monthly active users, changes between months, e.g., a table named ‘logins’ is shown as below.
| userz-id | date |
| 1 | 2020-07-01 |
| 234 | 2020-07-02 |
| 3 | 2020-07-02 |
| 1 | 2020-07-02 |
| … | … |
| 234 | 2020-10-04 |
Q: find the month-over-month percentage change for monthly active users
Solutions:
WITH mau AS
(
SELECT
DATE_TRUNC('month', date) AS month_timestamp,
COUNT(DISTINCT user_id) AS mau
FROM logins
GROUP BY
DATE_TRUNC('month', date)
)
SELECT
a.month_timestamp AS previous_month,
a.mau AS previous_mau,
b.month_timestamp AS current_month,
b.mau AS current_mau,
ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change
FROM mau a
JOIN mau b
ON a.month_timestamp = b.month_timestamp - interval '1 month'Next blog we will continue our journey with SQL medium-hard questions.
If you are interested in or have any problems with SQL, feel free to contact me .
Or you can connect with me through my LinkedIn.
