I have a SQL Table of logins as a data source, and each row has an id, timestamp and user_id.

Similar to this:

I want to build a report that shows an aggregate of logins by year. So something like (for all months, just using January as an example.):

Essentially, the active users would be grouping the rows of logins by user_id, and the logins would just aggregate the timestamps by month.

Is this kind of view something I build using a SQL query?

CodePudding user response：

You may use aggregation here:

SELECT
    YEAR(timestamp) AS Year,
    COUNT(DISTINCT user_id) AS `Active Users in January`,
    COUNT(*) AS `Logins in January`
FROM yourTable
WHERE
    MONTH(timestamp) = 1
GROUP BY
    YEAR(timestamp);

The number of active users is given by the distinct count of users for a given year, in the month of January. The number of logins is just the number of entries for a given year in January.

If you want to report for all months and all years, then use:

SELECT
    DATE_FORMAT(timestamp, '%Y-%m') AS ym,
    COUNT(DISTINCT user_id) AS `Active Users per month`,
    COUNT(*) AS `Logins in month`
FROM yourTable
GROUP BY 1;