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;