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

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.

>Solution :

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
);