I have some data that I pull as follows:
select date_key, count(id) from table1 where date_key between x and y group by date_key;
I would like to get some other data as follows:
select date_key2, count(id) from table2 where date_key2 between x-7 days and y-7 days group by date_key2;
I would then like to divide one by the other and get the result. To this end, I tried:
I.Converts /(SELECT J.Starts FROM (SELECT t2.`date_key`,
count(id) AS Starts
FROM `table2` t2
WHERE date_key BETWEEN 20140225 AND 20140303
GROUP BY t2.`date_key`)J)
count(id) AS Converts
FROM `table` t1
WHERE date_key BETWEEN 20140304 AND 20140411
GROUP BY t1.`date_key`)I;
I get a Subquery returns more than 1 row error. When I run them individually, ie, select the separate parts and run them, they run fine, but together, it crashes. What am I doing wrong?
To get your query working, you'll probably want to do a join. Basically do each grouping query separately and then join them together at the end.
In the query that follows I am making the assumption that you want to calculate a daily conversion rate. So for example, users that start on Day 1 are eligible to become 'Converts' on Day 8.
SELECT I.date_key, I.Converts, I.Converts / J.`Starts` FROM ( SELECT t2.`date_key`, count(id) AS `Starts` FROM `table2` AS t2 WHERE date_key BETWEEN '2014-02-25' AND '2014-03-03' GROUP BY t2.`date_key` ) AS J JOIN ( SELECT t1.`date_key`, count(id) AS Converts FROM `table` AS t1 WHERE date_key BETWEEN '2014-03-04' AND '2014-04-11' GROUP BY t1.`date_key` ) AS I ON I.`date_key`=DATE_ADD(J.`date_key`, INTERVAL 7 DAY);
Note the day offset being applied at the bottom. This telling SQL to join each Converts date with the Starts date from 7 days prior.