Console
    Start free

    Generate SQL query

    Generate a SQL query to count and sort unique logins in the last month

    You can query a model directly and test the results returned when using different parameter values with the Cloud console, or by calling the Vertex AI API directly.

    Prompt Freeform

    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.

    Response

    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 placesin 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:00up 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' 
 )
    Model: gemini-2.5-pro
    Temperature: 0.2
    Max output tokens: 8192
    TopK: 40.0
    TopP: 0.95
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: