How can I combine 2 SQL queries and get a cumulative score?

Question

I am trying to get the number of visits and the number of conversions every day for a specific combination of A / B tests. Each combination represents a different version of the A / B test. Here I use only '1' and '2' to represent variations, but technically there may be more options.

I wrote the following 2 queries that work independently. Is it possible to combine these or write a single query that retrieves the necessary data?

attends a request:

 SELECT DATE(visit.created), visit.combination, COUNT(visit.id) FROM visit WHERE visit.user_id = 6 AND visit.experiment_id = 1 GROUP BY DATE(visit.created), visit.combination 

visits the result:

conversion request:

 SELECT DATE(conversion.created), conversion.combination, COUNT(conversion.id) FROM conversion WHERE conversion.user_id = 6 AND conversion.experiment_id = 1 AND conversion.goal_id = 1 GROUP BY DATE(conversion.created), conversion.combination 

Conversion result

:

It would also be great if I could get the current total (cumulative) quantity, as shown below, in the last two columns. I grouped the table below using a combination, so that cumulative calculations are easier to understand:

 +---------------+-------------+----------------------+-----------------+--------------+--------------+ | DATE(created) | combination | COUNT(conversion.id) | COUNT(visit.id) | cumulative_c | cumulative_v | +---------------+-------------+----------------------+-----------------+--------------+--------------+ | 2015-11-17 | 1 | 1 | 3 | 1 | 3 | | 2015-11-18 | 1 | 7 | 4 | 8 | 7 | | 2015-11-19 | 1 | 3 | 8 | 11 | 15 | | 2015-11-17 | 2 | 4 | 1 | 4 | 1 | | 2015-11-18 | 2 | 2 | 6 | 6 | 7 | | 2015-11-19 | 2 | 9 | 6 | 15 | 13 | +---------------+-------------+----------------------+-----------------+--------------+--------------+ 

Database Schema:

Answer 1

Alignment is pretty simple: add 0-digit columns, make UNION_ALL , then group and summarize again.

 SELECT dt, combination, SUM(v_count) as v_count, SUM(c_count) as c_count FROM ( SELECT DATE(visit.created) as dt, visit.combination as combination, COUNT(visit.id) as v_count, 0 as c_count FROM visit WHERE visit.user_id = 6 AND visit.experiment_id = 1 GROUP BY DATE(visit.created), visit.combination UNION ALL SELECT DATE(conversion.created) as dt, conversion.combination as combination, 0 as v_count, COUNT(conversion.id) as c_count FROM conversion WHERE conversion.user_id = 6 AND conversion.experiment_id = 1 AND conversion.goal_id = 1 GROUP BY DATE(conversion.created), conversion.combination ) as t GROUP BY dt, combination 

Now, to accomplish everything. In a more advanced DBMS, this is called a "window" or "analytical" function. For example, in Oracle you can do this:

 SELECT dt, combination, SUM(v_count) OVER (PARTITION BY combination ORDER BY dt) as v_cumulative 

for the above request and it will give you what you want. However, MySQL does not have such functions. There are methods described here and here , but they are quite complex.

Answer 2

Hope this helps.

 SELECT DATE(v.created), v.combination ,DATE(c.created), c.combination, COUNT(v.id), COUNT(c.id) FROM user u LEFT JOIN conversion c ON u.id = c.user_id LEFT JOIN visit v ON u.id = v.user_id WHERE c.id = 6 AND v.experiment_id = 1 AND c.goal_id = 1 GROUP BY DATE(v.created), v.combination, DATE(c.created), c.combination