Generate SQL Query

• 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.

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.