33 lines
943 B
MySQL
33 lines
943 B
MySQL
|
/* -----------------------------------------------------------------------------
|
||
|
|
* Find all new hosts who joined in the last year (ignoring those with queued
|
||
|
|
* shows in the future)
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
SELECT h.hostid,
|
||
|
|
h.host,
|
||
|
|
min(e.date) AS joindate,
|
||
|
|
count(e.id) AS COUNT
|
||
|
|
FROM eps e
|
||
|
|
JOIN hosts h ON e.hostid = h.hostid
|
||
|
|
GROUP BY h.hostid
|
||
|
|
HAVING min(e.date) >= (curdate() - INTERVAL 364 DAY)
|
||
|
|
AND min(e.date) <= curdate()
|
||
|
|
ORDER BY min(e.date);
|
||
|
|
|
||
|
|
/* -----------------------------------------------------------------------------
|
||
|
|
* Total shows produced by the new hosts in the past year
|
||
|
|
*/
|
||
|
|
SELECT sum(COUNT) AS total_shows
|
||
|
|
FROM
|
||
|
|
(SELECT h.hostid,
|
||
|
|
h.host,
|
||
|
|
min(e.date) AS joindate,
|
||
|
|
count(e.id) AS COUNT
|
||
|
|
FROM eps e
|
||
|
|
JOIN hosts h ON e.hostid = h.hostid
|
||
|
|
GROUP BY h.hostid
|
||
|
|
HAVING min(e.date) >= (curdate() - INTERVAL 364 DAY)
|
||
|
|
AND min(e.date) <= curdate()
|
||
|
|
ORDER BY min(e.date)) src;
|
||
|
|
|