sqlgoogle-bigqueryaggregation

SQL - Group and Aggregation to calculate ratio with respect to total

My table looks like below:

product offer

Product1 O1

Product1 O1

Product1 O2

Product2 O3

Product2 O4

Where product is a product listed on website and offer is the offer for the product viewed by customer.

What I want is, a query to show percentage of each offer view with respect to total offer views for that product. So the result should look like below:

Product Offer ViewPercentage

Product1 O1 67%

Product1 O2 33%

Product2 O3 50%

Product2 O4 50%

Solution

  • You can use window functions to compute the view percentage for each offer:

    SELECT DISTINCT product, offer,
     100.0 * (COUNT(*) OVER (PARTITION BY product, offer)) / (COUNT(*) OVER (PARTITION BY product)) AS ViewPercentage
FROM offers