self join

Date: April 25th 2016
Last updated: April 25th 2016

SELECT
  date(g1.created_at) as dt,
  ROUND(100 * COUNT(DISTINCT g2.user_id) /
    COUNT(DISTINCT g1.user_id)) as retention
FROM gameplays AS g1
  LEFT JOIN gameplays AS g2 ON
    g1.user_id = g2.user_id
    AND DATE(g1.created_at) = DATE(DATETIME(g2.created_at, '-1 day'))
GROUP BY 1    
ORDER BY 1
LIMIT 100;