Counting records with related records which appear first in a given date

Question

I have two tables, players and games, created as follows:

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player` int(11) NOT NULL,
  `played_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I wish to extract 3 values for each day:

1. The number of players created at that day
2. The number of players played at that day
3. The number of players having played for the first time at that day

So, suppose for example that the players table looks as follows:

+----+--------+---------------------+
| id | name   | created_at          |
+----+--------+---------------------+
|  1 | Alan   | 2016-02-01 00:00:00 |
|  2 | Benny  | 2016-02-01 06:00:00 |
|  3 | Calvin | 2016-02-02 00:00:00 |
|  4 | Dan    | 2016-02-03 00:00:00 |
+----+--------+---------------------+

And the games table looks as follows:

+----+--------+---------------------+
| id | player | played_at           |
+----+--------+---------------------+
|  1 |      1 | 2016-02-01 01:00:00 |
|  2 |      3 | 2016-02-02 02:00:00 |
|  3 |      2 | 2016-02-03 14:00:00 |
|  4 |      3 | 2016-02-03 17:00:00 |
|  5 |      3 | 2016-02-03 18:00:00 |
+----+--------+---------------------+

Then the query should return something like

+------------+-----+--------+-------+
| day        | new | played | first |
+------------+-----+--------+-------+
| 2016-02-01 | 2   | 1      | 1     |
| 2016-02-02 | 1   | 1      | 1     |
| 2016-02-03 | 1   | 2      | 1     |
+------------+-----+--------+-------+

I have a solution for 1 (new):

SELECT Date(created_at) AS day,
       Count(*)         AS new
FROM   players
GROUP  BY day;  

That's easy. I think I also have a solution for 2 (played), thanks to MySQL COUNT DISTINCT:

select Date(played_at) AS day,
       Count(Distinct player) AS played
FROM   games
GROUP  BY day;

But I have no idea how to get the needed result for 3 (first). I also don't know how to put everything in a single query, to save execution time (the games table may include millions of records).

---

In case you need it, here's a query which inserts the example data:

INSERT INTO `players` (`id`, `name`, `created_at`) VALUES
(1, 'Alan', '2016-02-01 00:00:00'),
(2, 'Benny', '2016-02-01 06:00:00'),
(3, 'Calvin', '2016-02-02 00:00:00'),
(4, 'Dan', '2016-02-03 00:00:00');

INSERT INTO `games` (`id`, `player`, `played_at`) VALUES
(1, 1, '2016-02-01 01:00:00'),
(2, 3, '2016-02-02 02:00:00'),
(3, 2, '2016-02-03 14:00:00'),
(4, 3, '2016-02-03 17:00:00'),
(5, 3, '2016-02-03 18:00:00');

Answer 1

One version is to get all relevant data into a union and do the analysis from there;

SELECT SUM(type='P') new, 
       COUNT(DISTINCT CASE WHEN type='G' THEN pid END) played, 
       SUM(type='F') first 
FROM (
  SELECT id pid, DATE(created_at) date, 'P' type FROM players 
  UNION ALL 
  SELECT player, DATE(played_at) date,  'G' FROM games 
  UNION ALL 
  SELECT player, MIN(DATE(played_at)),  'F' FROM games GROUP BY player
) z 
GROUP BY date;

In the union;

Records with type P is player creation statistics.
Records with type G is player related game statistics.
Records with type F is statistics for when players played their first game.

Answer 2

You can count the result of a temp table based on min(played_at) and filterd by having

select count(player) from 
   (  select player, min(played_at)  
      from games 
      group by player 
      having min(played_at) = YOUR_GIVEN_DATE ) as t;

Answer 3

this query will give you the result:

select day,( select count(distinct(id)) from players where Date(created_at) = temp.day ) as no_created_at ,
( select count(distinct(player)) from games where Date(played_at) = temp.day) as no_played_at,
( select count(distinct(player)) from games  where Date(played_at) = 
(select min(Date(played_at)) from games internal_games 
where internal_games.player =games.player and Date(games.played_at) = temp.day )) as no_first_played_at
 from (
SELECT Date(created_at) AS day     
FROM   players
GROUP  BY day 
union 
select Date(played_at) AS day
FROM   games
GROUP  BY day) temp 

and the output:

Answer 4

Here's a solution with a bunch of subqueries, which accounts for the possibility that players may have been created on days with no games, and vice versa:

select
    all_dates.date as day,
    ifnull(new.num, 0) as new,
    ifnull(players.num, 0) as players,
    ifnull(first.num, 0) as first
from (
    select date(created_at) as date from players
    union
    select date(played_at) from games
) as all_dates
left join (
    select date(created_at) as created_at_date, count(*) as num
    from players
    group by created_at_date
) as new on all_dates.date = new.created_at_date
left join (
    select date(played_at) as played_at_date, count(distinct player) as num
    from games
    group by played_at_date
) as players on all_dates.date = players.played_at_date
left join (
    select min_date, count(*) num
    from (
        select player, date(min(played_at)) as min_date
        from games
        group by player
    ) as players_first
    group by min_date
) as first on all_dates.date = first.min_date
order by day;