Counting records with corresponding records that appear first on a given date

Question

I have two tables, playersand gamescreated 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 want to extract 3 values ​​for every day:

• Number of players created on this day
• The number of players playing on this day
• Number of players playing for the first time on this day

So, suppose, for example, that the table of players looks like this:

+----+--------+---------------------+
| 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 table of games is 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 request 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;  

It is easy. I think I also have a solution for 2 (playable) thanks to MySQL COUNT DISTINCT :

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

, 3 (). , , ( games ).

---

, , :

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

- ;

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;

;

P - .
G - , .
F - , .

Answer 2

min (play_at) filterd,

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

:

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 

:

Answer 4

There is a solution with a bunch of subqueries that takes into account the likelihood that players can be created on days without 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;