CODEWITHSUNDEEP
mysql
stack
stack

Reputation: 10228

How to count the number of first transactions of each user per day?

I have a table named transactions which contains all transactions. Something like this:

// transactions
+--------+-------------+---------+-------------+---------------------+
|  id    | business_id | user_id |   amount    |      created_at     |
+--------+-------------+---------+-------------+---------------------+
| 1      | 503         | 12      | 4500        | 2021-04-15 04:22:09 |
| 2      | 33          | 12      | 1200        | 2021-04-17 12:22:10 |
| 3      | 771         | 13      | 400         | 2021-04-18 13:02:18 |
| 4      | 86          | 14      | 7500        | 2021-04-18 16:07:12 | 
| 5      | 772         | 13      | 3400        | 2021-04-23 07:11:04 |
| 6      | 652         | 14      | 900         | 2021-04-24 10:35:08 |
| 7      | 567         | 15      | 1000        | 2021-04-24 14:55:11 |
+--------+-------------+---------+-------------+---------------------+

I need to count the number of users that have had their first transaction per day. So here is the expected result:

// The expected result
+------+-------+-------+--------------------------+
| year | month |  day  |  first_transactions_num  |
+------+-------+-------+--------------------------+
| 2021 | 04    | 15    | 1                        |
| 2021 | 04    | 18    | 2                        |
| 2021 | 04    | 24    | 1                        |
+------+-------+-------+--------------------------+

Any idea how can I do that?

Here's what I've tried:

SELECT year(created_at) year,
       month(created_at) month,
       day(created_at) day,
       count(1) num
FROM transactions
GROUP BY year, month, day

But my query doesn't care about "the first transaction of each user".

Upvotes: 0

Views: 474

Answers (1)

forpas
forpas

Reputation: 164139

You should aggregate on the results of this query:

SELECT MIN(created_at) created_at
FROM transactions
GROUP BY user_id

which returns the 1st transaction of each user.
So do it like this:

SELECT YEAR(created_at) year,
       MONTH(created_at) month,
       DAY(created_at) day,
       COUNT(*) num
FROM (
  SELECT MIN(created_at) created_at
  FROM transactions
  GROUP BY user_id
) t
GROUP BY year, month, day

Or better:

SELECT YEAR(created_at) year,
       MONTH(created_at) month,
       DAY(created_at) day,
       COUNT(*) num
FROM (
  SELECT DATE(MIN(created_at)) created_at
  FROM transactions
  GROUP BY user_id
) t
GROUP BY created_at

Or if your version of MySql is 8.0+ use COUNT() window function:

SELECT DISTINCT 
       YEAR(MIN(created_at)) year,
       MONTH(MIN(created_at)) month,
       DAY(MIN(created_at)) day,
       COUNT(*) OVER (PARTITION BY DATE(MIN(created_at))) num
FROM transactions
GROUP BY user_id

See the demo.

Upvotes: 1

Related Questions