Sharath Sharath - 3 months ago 8
SQL Question

Compute number of users clicking different events in MySQL

I have a table called "alerts" that looks like this.

user_id event alert_type eventtime
1 clk s January, 19 2037 12:16:21
2 clk b January, 19 2037 00:28:25
3 clk b January, 19 2037 02:44:23
3 clk b January, 19 2037 00:16:25
1 clk b January, 19 2037 02:28:25
9 clk s January, 19 2037 22:40:25
9 clk s January, 19 2037 03:14:07
2 clk b January, 19 2037 03:22:07
6 clk b January, 19 2037 05:21:07
9 clk p January, 19 2037 15:24:07
3 clk p January, 19 2037 05:34:07
10 clk s January, 19 2037 12:39:07
4 clk b January, 19 2037 09:14:07
4 clk s January, 19 2037 09:34:07
1 clk s January, 19 2037 09:54:07
8 clk b January, 19 2037 13:14:07
8 clk p January, 19 2037 10:24:07
8 clk p January, 19 2037 13:45:07
10 clk b January, 19 2037 13:51:07
4 clk b January, 19 2037 14:14:07
12 clk p January, 19 2037 15:14:07
1 clk s January, 19 2037 00:44:07
10 clk s January, 19 2037 08:14:07
12 clk s January, 19 2037 16:24:07
7 clk p January, 19 2037 06:53:07
6 clk b January, 19 2037 20:14:07
7 clk b January, 19 2037 20:23:07


Given an alert_type, I am trying to compute how many users clicked an alert as their first alert in the given day.

My desired output is

alert_type Count of Users
b 4
p 3
s 3


I am trying to do it this way but I am not getting exactly what I want

SELECT count(user_id) AS Count of Users, alert_type, MIN(eventtime)
FROM alerts
GROUP BY alert_type
ORDER BY alert_type DESC;


Kindly provide some inputs to achieve the desired output.

Answer

Here is one method to get the first alert per user in a day:

select alert_type, count(*) as `Total Number of Users`
from alerts a
where a.eventtime = (select min(a2.eventtime)
                     from alerts a2
                     where a2.user_id = a.user_id and
                           date(a2.eventtime) = date(a.eventtime)
                    )
group by alert_type;