I am trying to calculate daily % - which is count(distinct id)/ total distinct id for that variant. I am not sure how to do that in SQL
Here is my input
| date | variant | operator | id |
|---|---|---|---|
| 1-Nov | mp | op 1 | 1 |
| 1-Nov | mp | op 2 | 1 |
| 1-Nov | mp | op 2 | 2 |
| 1-Nov | mp | op 1 | 3 |
| 2-Nov | mp | op 1 | 2 |
| 2-Nov | cp | op 1 | 2 |
| 4-Nov | mp | op 2 | 3 |
| 5-Nov | mp | op 3 | 3 |
| 6-Nov | mp | op 1 | 3 |
| 6-Nov | cp | op 1 | 3 |
| 7-Nov | mp | op 2 | 4 |
| 8-Nov | mp | op 1 | 3 |
| 9-Nov | mp | op 2 | 5 |
| 9-Nov | cp | op 2 | 5 |
and this is how I would like my output to be
| date | variant | operator | daily_distinct_count | total_distinct_count | %_calc |
|---|---|---|---|---|---|
| 1-Nov | mp | op1 | 2 | 5 | 0.40 |
| 1-Nov | mp | op2 | 2 | 5 | 0.40 |
| 2-Nov | mp | op1 | 1 | 5 | 0.20 |
| 2-Nov | cp | op1 | 1 | 3 | 0.33 |
| 4-Nov | mp | op 2 | 1 | 5 | 0.20 |
| 5-Nov | mp | op 3 | 1 | 5 | 0.20 |
| 6-Nov | mp | op 1 | 1 | 5 | 0.20 |
| 6-Nov | cp | op 1 | 1 | 3 | 0.33 |
| 7-Nov | mp | op 2 | 1 | 5 | 0.20 |
| 8-Nov | mp | op 1 | 1 | 5 | 0.20 |
| 9-Nov | mp | op 2 | 1 | 5 | 0.20 |
| 9-Nov | cp | op 2 | 1 | 3 | 0.33 |
Ideally, I am looking to calculate % on a daily level for each operator - the denominator is count distinct id for each variant
How can I achieve this Here is what I tried to get till the count
select date,
variant,
operator,
count(distinct id) as daily_distinct_count
from t1
group by 1,2,3
Not sure how to proceed from here
CodePudding user response：
You should be able to do:
select date,
variant,
operator,
count(distinct id) as daily_distinct_count,
total_distinct_count,
count(distinct id) / total_distinct_count as percent_calc
from t1
join (
select variant, count(distinct id) as total_distinct_count
from t1
group by 1
) variant_distinct_ids using (variant)
group by 1,2,3