<aside> ❓ How to calculate AVG or MEDIAN from aggregate distinct count
</aside>
Example:
We have users, properties and documents in a joined table.
One user might have multiple properties and one property might have multiple documents
User ID | Property ID | Document ID | Distinct Count |
---|---|---|---|
A | P1 | D1 | 2 |
A | P2 | D2 | 2 |
B | P3 | D3 | 1 |
C | P4 | D4 | 1 |
C | P4 | D5 | 1 |
C | P4 | D6 | 1 |
Based on the Data the above we have 3 users, 4 properties and 6 documents but only 2 or 1 property per user
Correct AVG is 1.5
A = 2
B = 1
C = 1
Incorrect AVG is 2.5
A = 4
B = 1
C = 3