Katkat
Back

Find least active countries

MetaData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question tests the ability to perform multi-step data aggregation using SQL, specifically focusing on joining, filtering by date ranges, calculating distinct counts, and identifying bottom-tier performers across different time periods.

Problem Statement

You are given two tables: users and events.

Table users contains:

  • user_id (int): Unique identifier for the user.
  • country (varchar): The country where the user is based.

Table events contains:

  • event_id (int): Unique identifier for an event.
  • user_id (int): Foreign key referring to users.
  • event_date (date): The date the event occurred.

Task

Write a SQL query to identify the top 3 countries with the lowest number of unique active users in the year 2023.

An "active user" is defined as any user who has at least one event record in the events table during the year 2023. If a country has zero active users, it should be excluded from the final list. Your output should display the country name and the count of unique active users, sorted by the count in ascending order.

Example Input

users:

user_idcountry
1USA
2USA
3Canada
4Mexico

events:

event_iduser_idevent_date
10112023-01-01
10222023-02-01
10332023-03-01

Expected Output

countryunique_users
Canada1
USA2

Constraints & Notes

  • Only consider events where event_date falls within '2023-01-01' and '2023-12-31'.
  • Handle ties by alphabetically sorting the country name if counts are equal.
  • Ensure you only count unique user_ids per country.

Hints

Hint 1

Join the `users` and `events` tables on `user_id` to associate each event with a country.

Hint 2

Filter the joined result to include only events that occurred in the year 2023 using the `WHERE` clause.

Hint 3

Use `GROUP BY` on the country column and apply `COUNT(DISTINCT user_id)` to get the number of unique active users per country.

Follow-up questions

  • How would you modify this query to include countries with zero active users, displaying them with a count of 0?
  • If the `events` table contains billions of rows, how would you optimize this query to run efficiently?
  • How would you change the query to compare the number of unique active users in 2023 versus 2022 to identify the fastest-declining countries?