Générer une requête SQL pour compter et trier les connexions uniques au cours du mois dernier
|
Vous pouvez interroger un modèle directement et tester les résultats renvoyés lors de l'utilisation de différentes valeurs de paramètre en utilisant la console Cloud ou en appelant directement l'API Vertex AI.
|
|||||||||||
Invite forme 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."
Réponse
```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;
```
|
|
||||||||||