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

WITH MonthlyLogins AS (
SELECT
Username,
LicenseType,
DATE_TRUNC('month', LoginTime) AS LoginMonth,
COUNT(DISTINCT DATE(LoginTime)) AS UniqueLoginDays
FROM your_table
WHERE LoginTime >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3 month')
GROUP BY Username, LicenseType, LoginMonth
)
SELECT
Username,
LicenseType,
SUM(UniqueLoginDays) AS TotalUniqueLoginDays
FROM MonthlyLogins
GROUP BY Username, LicenseType
ORDER BY TotalUniqueLoginDays DESC;

Explanation:

  1. WITH MonthlyLogins AS (...): This defines a Common Table Expression (CTE) named MonthlyLogins to calculate the unique login days for each user and license type within each month.
  2. SELECT ... FROM your_table WHERE LoginTime >= ...: This selects the relevant data from your table, filtering for login events within the last 3 months.
  3. DATE_TRUNC('month', LoginTime) AS LoginMonth: This extracts the beginning of the month from the LoginTime column and aliases it as LoginMonth.
  4. COUNT(DISTINCT DATE(LoginTime)) AS UniqueLoginDays: This counts the distinct dates of logins within each month, giving us the number of unique login days.
  5. GROUP BY Username, LicenseType, LoginMonth: This groups the results by username, license type, and login month.
  6. SELECT ... FROM MonthlyLogins GROUP BY Username, LicenseType: This selects the username, license type, and sums the UniqueLoginDays for each user and license type across all months.
  7. ORDER BY TotalUniqueLoginDays DESC: This sorts the results in descending order based on the total number of unique login days.

Note:

  • Replace your_table with the actual name of your table.
  • This query assumes your LoginTime column is a timestamp or datetime data type.
  • The DATE_TRUNC function may vary depending on your specific database system.

This query will provide you with a list of users, their license types, and the total number of unique days they logged in during the last 3 months, sorted from the highest to the lowest number of login days.

Model: gemini-1.5-flash-001
Temperature: 1
Max output tokens: 8192
TopK: 40
TopP: 0.95