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:
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');
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With