Research talk:Teahouse long term new editor retention/Work log/2016-04-13

Wednesday, April 13, 2016

Double checking how many dates during both invites sample periods there are where invites were NOT sent.

#for TH2 (October 20 2014 - January 12 2015)

SELECT s_day from
(
SELECT ADDDATE('2014-10-20', INTERVAL @i:=@i+1 DAY) AS s_day
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2015-01-12', '2014-10-20') 
) as all_dates where s_day not in (select distinct(date(sample_date)) from th_retention_sample);

Output:

2014-10-23
2014-10-27
2014-11-06
2014-11-12
2014-11-21
2014-11-23
2014-11-24
2014-12-06
2014-12-10
2014-12-15
2014-12-18
2014-12-21
2015-01-08
2015-01-09

#14 days where invites were not sent

#for TH3 (November 2 2015 - December 14 2015)

SELECT s_day from
(
SELECT ADDDATE('2015-11-02', INTERVAL @i:=@i+1 DAY) AS s_day
FROM (
SELECT a.a
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2015-12-14', '2015-11-02') 
) as all_dates where s_day not in (select distinct(date(sample_date)) from th_retention_sample_2);

Output:

2015-12-12
2015-12-13

#2 days where invites were not sent