Question 240876 in Mysql Question posted in
The official MySQL documentation can be found here.

counting smth in betweeen 1 month of register in mysql

I want to count some data in a month after a specific date.
the table looks like this

date user_id tx_id
2022-07-08 123 1
2022-07-08 124 2
2022-07-10 123 3

I want to count every tx_id that user_id number 123 made in the a month from it’s first tx_id. like how many tx_id s does user 123 made from 2022-07-08 to 2022-08-08?

I couldn’t find any thing that can handle both finding if it’s first tx_id and counting the other tx_ids one month from that date.

Tags:

3

Answers


  1. 0

    use this query :

    SELECT COUNT(*) AS transaction_count
FROM your_table
WHERE user_id = 123
AND tx_id IN (
    SELECT tx_id
    FROM your_table
    WHERE user_id = 123
    ORDER BY date ASC
    LIMIT 1
)
AND date >= (
    SELECT MIN(date)
    FROM your_table
    WHERE user_id = 123
)
AND date <= (
    SELECT DATE_ADD(MIN(date), INTERVAL 1 MONTH)
    FROM your_table
    WHERE user_id = 123
);

    here is the optimized query without subquery:

    SELECT COUNT(*) AS transaction_count
FROM your_table
WHERE user_id = 123
AND date >= (
    SELECT MIN(date)
    FROM your_table
    WHERE user_id = 123
)
AND date <= DATE_ADD(
    (
        SELECT MIN(date)
        FROM your_table
        WHERE user_id = 123
    ),
    INTERVAL 1 MONTH
);
    Login or Signup to reply.
  2. 0

    How about grouping by user_id? Then count?
    Count the number of transactions made by each user in the first month of their transaction.

    SELECT t.user_id, COUNT(t.tx_id) AS tx_count FROM transactions t JOIN first_tx f ON t.user_id = f.user_id WHERE t.tx_date BETWEEN f.first_tx_date AND f.first_tx_date + INTERVAL ‘1 MONTH’ GROUP BY t.user_id;

    eg

    user_id tx_count
    1 3
    2 3
    3 10
    Login or Signup to reply.
  3. 0

    Here is a general solution: Use NOT EXISTS to only get the first month’s orders per user. Then count found rows per user.

    select user_id, count(*)
from mytable
where not exists
(
  select null
  from mytable older
  where older.user_id = mytable.user_id
  and older.date < mytable.date - interval 1 month
)
group by user_id
order by user_id;

    Here is the same with getting the first date per user and using <=:

    select user_id, count(*)
from mytable
where date <=
(
  select min(date) + interval 1 month
  from mytable first_month
  where first_month.user_id = mytable.user_id
)
group by user_id
order by user_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search