Can I speed up this slow query which should be limited by MIN/MAX values?

Question

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.

Answer 1

This should work:

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
  HAVING first >= ? AND last < ?);