CODEWITHSUNDEEP
sqlselectcountamazon-redshiftwindow-functions
scott martin
scott martin

Reputation: 1293

Redshift - Find percentage of contribution by category

I have my data in the below format:

product, person
prod_a, person_1
prod_a, person_2
prod_a, person_3
prod_b, person_1

I am trying to find the percent contributed by each person per product. Expected output is :

product, person, prct
prod_a, person_1, 0.33
prod_a, person_2, 0.33
prod_a, person_3, 0.33
prod_b, person_1, 1

Upvotes: 0

Views: 256

Answers (1)

GMB
GMB

Reputation: 222462

You can use window functions:

select
    product,
    person,
    1.0 * count(*) over(partition by product, person) 
        / count(*) over(partition by product) as prct
from mytable

If, as shown in your sample data, there are no duplicate (product, person) tuples, then it is a bit simpler:

select
    product,
    person,
    1.0 / count(*) over(partition by product) as prct
from mytable

Upvotes: 1

Related Questions