The intention here is to look at the user retention over a period of time.
My current solution employs a temporary table, since I do this over several ranges.
CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT); INSERT INTO user_retention_temp (SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id FROM day_item GROUP BY player_id);
Here I would have liked to limit this query to only match player_id's that has a first/last that's in a specific period of time.
Instead I'm force to do that for the consequent query:
SELECT LEAST(last - first, 7) AS diff, count(*) AS amount FROM user_retention_temp WHERE first >= ? AND last < ? GROUP BY diff
This is pretty awful since I'm filling up the temporary table with lots of data I don't need.
Is there a faster way? Assume at least 2-3 million entries in
day_item and that everything we need is indexed.