Medium-Hard Data Analyst SQL questions(Part 1)

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

table:logins

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.