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-iddate
12020-07-01
2342020-07-02
32020-07-02
12020-07-02
2342020-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.

Author: Jacqui

Data Science|Business Intelligence

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: