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.
Q: find the month-over-month percentage change for monthly active users
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.