Q#104: Percent of months logged in

Question Curteousy of InterviewQs

You’re given the following table showing user logins to your system:

Table: login_info

Using the table above, write a SQL query that returns percent of months each user has logged in to your system. Your numerator will be the number of months a user has logged in, and your denominator will be the total number of months that a user has been registered in your system (here you can use the user’s last login — first login as a proxy for this metric).

Here’s an example of what your output table should look like:

TRY IT YOURSELF

ANSWER

When analyzing user engagement in a system, one crucial metric is the frequency of logins over a period. For this task, we need to calculate the percentage of months each user has logged into our system, considering the entire period they have been registered. Let’s explore how to achieve this using SQL.

Problem Breakdown

To calculate the percentage of months each user has logged in, follow these steps:

1. Determine the months each user logged in.
2. Calculate the total number of months each user has been registered.
3. Compute the percentage by dividing the number of months logged in by the total number of months registered.

Step-by-Step Solution

1. Extract the Month and Year of Each Login

First, we’ll extract the month and year from each login date. This will help us to determine distinct months a user has logged in.

2. Calculate the Total Number of Months Registered

We need to find the period each user has been registered by identifying their first and last login dates. The difference between these dates in terms of months gives us the total months registered.

3. Compute the Percentage

Finally, we’ll compute the percentage of months a user has logged in by dividing the count of distinct login months by the total registered months.

SQL Query

Here is the SQL query that performs the above steps:

WITH user_months AS (
    SELECT 
        user_id, 
        EXTRACT(YEAR FROM date) AS login_year, 
        EXTRACT(MONTH FROM date) AS login_month
    FROM login_info
    GROUP BY user_id, login_year, login_month
), 
user_first_last_login AS (
    SELECT 
        user_id, 
        MIN(date) AS first_login, 
        MAX(date) AS last_login
    FROM login_info
    GROUP BY user_id
), 
user_month_counts AS (
    SELECT 
        ufl.user_id, 
        COUNT(DISTINCT um.login_year || '-' || um.login_month) AS distinct_login_months,
        EXTRACT(YEAR FROM ufl.last_login) * 12 + EXTRACT(MONTH FROM ufl.last_login) -
        (EXTRACT(YEAR FROM ufl.first_login) * 12 + EXTRACT(MONTH FROM ufl.first_login)) + 1 AS total_registered_months
    FROM user_first_last_login ufl
    JOIN user_months um
    ON ufl.user_id = um.user_id
    GROUP BY ufl.user_id, ufl.first_login, ufl.last_login
)
SELECT 
    user_id, 
    (distinct_login_months * 100.0 / total_registered_months) AS pct_months
FROM user_month_counts;

Explanation

1. user_months CTE:

• Extracts the year and month from the login dates and groups by user_id, login_year, and login_month to get distinct login months.

1. user_first_last_login CTE:

• Determines the first and last login dates for each user.

1. user_month_counts CTE:

• Calculates the number of distinct login months (distinct_login_months) for each user.
• Calculates the total number of months a user has been registered (total_registered_months) by converting the first and last login dates into a continuous month format and finding their difference.

1. Final SELECT:

• Computes the percentage of months a user has logged in by dividing distinct_login_months by total_registered_months and multiplying by 100.

Plug: Checkout all my digital products on Gumroad here. Please purchase ONLY if you have the means to do so. Use code: MEDSUB to get a 10% discount!

Earn $25 and 4.60% APY for FREE through my referral at SoFi Bank Here