7

I'm trying to retrieve the number of visits and number of conversions each day for a particular A/B test combination. Each combination represents a different variation of an A/B test. Here I'm using just '1' and '2' to represent the variations, but technically there could be more variations.

I've written the following 2 queries, which work independently. Is it possible to combine these or write a single query that retrieves the data I want?

visits query:

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 result:

enter image description here

conversions query:

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

conversions result:

conversions result

Also it would be great if I could get retrieve a running total (cumulative) count, like below, see the last 2 columns. I've grouped the the table below by combination so the cumulative counts 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 |
+---------------+-------------+----------------------+-----------------+--------------+--------------+

The database schema:

enter image description here

matthew
  • 2,156
  • 5
  • 22
  • 38

2 Answers2

4

Combining is quite simple: add 0-value columns, do UNION_ALL, then group again and sum.

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, for running total. In more advanced DBMS this is called "window" or "analytic" functions. 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 query, and it would give you just what you want. However, MySQL doesn't have such functions. There are ways around, described here and here for example, but they are quite tricky.

Community
  • 1
  • 1
Timekiller
  • 2,946
  • 2
  • 16
  • 16
  • Many thanks Timekiller :) this works and I'm doing the running totals in PHP. I tried but it doesn't appear trivial to do the running totals for each combination in MySQL, but thanks for the links. – matthew Dec 15 '15 at 10:03
0

I hope this would help.

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
Yekta
  • 51
  • 5