Issue with aggregating data when data missing on certain dates

Question

How can I edit the following SQL query to get results for each day and each variant, where I have an A/B/Control test:

SELECT 
    t.day, t.type AS variant_type, t.clicks, 
    t2.type AS control, t2.control_clicks
FROM 
    table1 t
LEFT JOIN 
    table1 t2 ON t.day = t2.day
              AND t2.type = 'control'

Currently data will only come through where there is data for a given day in the from clause. This leads to issues when aggregating the data further down the line.

Current results:

day	variant	variant_clicks	control	control_clicks
2020-01-01	a	1000	control	1500
2020-01-01	b	2000	control	1500
2020-01-01	control	1500	control	1500
2020-01-02	control	1700	control	1700

Desired results:

day	variant	variant_clicks	control	control_clicks
2020-01-01	a	1000	control	1500
2020-01-01	b	2000	control	1500
2020-01-01	control	1500	control	1500
2020-01-02	a	0	control	1700
2020-01-02	b	0	control	1700
2020-01-02	control	1700	control	1700

Any help would be greatly appreciated!

Gordon Linoff · Accepted Answer · 2021-08-25 15:12:37Z

1

You can generate the rows using a cross join and then bring in existing data using left join:

SELECT d.day, v.type as variant_type,
       COALESCE(t.clicks, 0) as clicks, 'control' as control,
       MAX(t.clicks) FILTER (WHERE type = 'control') OVER (PARTITOIN BY d.day) as control_clicks
FROM (SELECT DISTINCT day FROM table1) d CROSS JOIN
     (VALUES ('a', 'b', 'control')) v(type) LEFT JOIN
     table1 t
     ON t.day = d.day AND t.type = v.type;

Note: This uses window functions to get the control clicks rather than a separate aggregation and join.

answered Aug 25, 2021 at 15:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlie B Over a year ago

Thank you for this @marc_s. Had thought of using a cross join for the days, just then wasn't sure what to do next Do you know if there's a way to do it without specifying the values? As I run tests with a differing number of variants - i.e. Control/A, Control/A/B/.../n, A/B/C (where we would use A in place of the control group)?

Gordon Linoff Over a year ago

@CharlieB . . . You can use a subquery . . . (select distinct type from table1).

Collectives™ on Stack Overflow

Issue with aggregating data when data missing on certain dates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related