Try Gemini 1.5 models, our newest multimodal models in Vertex AI, and see what you can build with a 1M token context window.Try Gemini 1.5 models, our newest multimodal models in Vertex AI, and see what you can build with a 1M token context window.
Stay organized with collections
Save and categorize content based on your preferences.
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.
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:
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.
SELECT ... FROM your_table WHERE LoginTime >= ...: This selects the relevant data from your table, filtering for login events within the last 3 months.
DATE_TRUNC('month', LoginTime) AS LoginMonth: This extracts the beginning of the month from the LoginTime column and aliases it as LoginMonth.
COUNT(DISTINCT DATE(LoginTime)) AS UniqueLoginDays: This counts the distinct dates of logins within each month, giving us the number of unique login days.
GROUP BY Username, LicenseType, LoginMonth: This groups the results by username, license type, and login month.
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.
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.
