Genera una consulta en SQL para contar y ordenar los accesos únicos en el último mes
|
Puedes consultar un modelo directamente y probar los resultados que se muestran cuando usas valores de parámetros diferentes con la consola de Cloud o con una llamada directa a la API de Vertex AI.
|
|||||||||||
Mensaje Formato libre
"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."
Respuesta
```sql
WITH MonthlyLogins AS (
SELECT
Username,
LicenseType,
DATE(LoginTime) AS LoginDate
FROM
YourTable
WHERE
LoginTime BETWEEN DATE('now', '-3 months') AND DATE('now')
),
UniqueLoginDays AS (
SELECT
Username,
LicenseType,
COUNT(DISTINCT LoginDate) AS UniqueLoginDays
FROM
MonthlyLogins
GROUP BY
Username,
LicenseType
)
SELECT
Username,
LicenseType,
UniqueLoginDays
FROM
UniqueLoginDays
ORDER BY
UniqueLoginDays DESC;
```
|
|
||||||||||