Generate SQL query

Consider a table with schema as described below.

' Column name - Description ' Username - username of the user logging in ' LicenseType - Either "MultiUse" or "SingleUse" ' LoginTime - The datetime of the login event

As data analysis expert, please write a SQL query to show me the list of users, their license types and the number of unique days on which the user logged in for the last 3 calendar months. Sort it by highest count of logins in a month to the lowest count of logins in a month.

Of course. This is an interesting request because the primary metric you want to display (NumberOfUniqueLoginDays) is different from the metric you want to sort by (MaxLoginsInAMonth). This requires a slightly more advanced query structure, for which a Common Table Expression (CTE) is a perfect and readable solution.

Here is the SQL query, followed by a detailed breakdown of how it works.

The SQL Query

This query is written using standard SQL functions where possible, with notes on how to adapt it for different database systems like SQL Server, PostgreSQL, and MySQL.

/*
This query uses a Common Table Expression (CTE) to first calculate the sorting criteria 
(the highest number of logins a user had in a single month) and then joins that 
back to the main data to calculate the final output.
*/
WITH MonthlyLoginCounts AS (
    -- Step 1: Calculate the total number of logins for each user in each of the last 3 months.
    SELECT
        Username,
        -- Use a function to get the Year and Month for grouping.
        -- For SQL Server: FORMAT(LoginTime, 'yyyy-MM')
        -- For PostgreSQL: TO_CHAR(LoginTime, 'YYYY-MM')
        -- For MySQL: DATE_FORMAT(LoginTime, '%Y-%m')
        -- For SQLite: STRFTIME('%Y-%m', LoginTime)
        FORMAT(LoginTime, 'yyyy-MM') AS LoginMonth, 
        COUNT(*) AS MonthlyLogins
    FROM
        UserLogins -- <-- Replace with your actual table name
    WHERE
        -- This WHERE clause selects data from the last 3 full calendar months.
        -- See dialect-specific examples below.
        LoginTime >= DATEADD(MONTH, -3, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) -- SQL Server example
        AND LoginTime < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) -- SQL Server example
    GROUP BY
        Username,
        FORMAT(LoginTime, 'yyyy-MM') -- SQL Server example
),
MaxMonthlyLogins AS (
    -- Step 2: Find the single highest monthly login count for each user from the step above.
    SELECT
        Username,
        MAX(MonthlyLogins) AS MaxLoginsInAMonth
    FROM
        MonthlyLoginCounts
    GROUP BY
        Username
)
-- Step 3: The final SELECT statement to produce the desired output.
SELECT
    ul.Username,
    ul.LicenseType,
    -- We calculate the number of unique days over the entire 3-month period here.
    COUNT(DISTINCT CAST(ul.LoginTime AS DATE)) AS NumberOfUniqueLoginDays
FROM
    UserLogins ul -- <-- Replace with your actual table name
-- We join our sorting criteria (MaxLoginsInAMonth) to the main table.
JOIN
    MaxMonthlyLogins mml ON ul.Username = mml.Username
WHERE
    -- We must apply the same date filter again.
    LoginTime >= DATEADD(MONTH, -3, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)) -- SQL Server example
    AND LoginTime < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) -- SQL Server example
GROUP BY
    ul.Username,
    ul.LicenseType,
    mml.MaxLoginsInAMonth -- Must be in GROUP BY to be used in ORDER BY
ORDER BY
    mml.MaxLoginsInAMonth DESC;

Breakdown of the Logic

1. The Challenge: You need to show the total unique login days over 3 months but sort by the peak login count within any single month in that period. We can't do this in one simple GROUP BY operation.

2. CTE 1: MonthlyLoginCounts:

   • This is the first logical step. We go through the login records for the last three full calendar months.
   • We GROUP BY both Username and the specific month (yyyy-MM) of the login.
   • This gives us a temporary table listing every user and their total login count for each month they were active (e.g., UserA | 2023-05 | 22 logins, UserA | 2023-06 | 15 logins).

3. CTE 2: MaxMonthlyLogins:

   • This CTE takes the result from the first step.
   • It groups by Username only and uses MAX(MonthlyLogins) to find the single highest monthly login count for each user.
   • The result is a simple list: Username and their personal best MaxLoginsInAMonth during the period. This is the value we will use for sorting.

4. Final SELECT Statement:

   • This is where we build the final output.
   • We select Username and LicenseType.
   • We use COUNT(DISTINCT CAST(LoginTime AS DATE)) to get the number of unique days a user logged in. CAST(LoginTime AS DATE) strips the time part, so multiple logins on the same day are counted only once.
   • We JOIN our main table with the MaxMonthlyLogins CTE on Username. This attaches the sorting value to each user.
   • The GROUP BY clause now includes Username, LicenseType, and the MaxLoginsInAMonth from our CTE.
   • Finally, ORDER BY MaxLoginsInAMonth DESC sorts the results exactly as requested.

Database-Specific WHERE Clauses

The most significant variation between SQL dialects is in date/time functions. Please replace the WHERE clause in both places in the query above with the appropriate version for your system.

Let's assume today is August 15, 2023. The query will correctly select data from May 1, 2023, 00:00:00 up to (but not including) August 1, 2023, 00:00:00.

For SQL Server:

WHERE LoginTime >= DATEADD(MONTH, -3, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
  AND LoginTime < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

For PostgreSQL:

Remember to also change FORMAT to TO_CHAR.

WHERE LoginTime >= date_trunc('month', NOW()) - INTERVAL '3 months'
  AND LoginTime < date_trunc('month', NOW())

For MySQL:

Remember to also change FORMAT to DATE_FORMAT and CAST(... AS DATE) to DATE(...).

WHERE LoginTime >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 3 MONTH)
  AND LoginTime < DATE_FORMAT(NOW(), '%Y-%m-01')

For SQLite:

Remember to also change FORMAT to STRFTIME.

WHERE LoginTime >= date('now', 'start of month', '-3 months')
  AND LoginTime < date('now', 'start of month')