This supposes to give me:
Sum of all KPI values for a distinct product SKU
Example table:
| Date | product_sku |page_views|number_of_subs|<br />
|------------|-------------|----------|--------------|<br />
| 2022-01-01 | 1 | 110 | 50 |<br />
| 2022-01-25 | 2 | 1000 | 40 |<br />
| 2022-01-20 | 3 | 2000 | 10 |<br />
| 2022-01-01 | 1 | 110 | 50 |<br />
| 2022-01-25 | 2 | 1000 | 40 |<br />
| 2022-01-20 | 3 | 2000 | 10 |
Expected Output:
<br />
| product_sku |page_views|number_of_subs|<br />
|-------------|----------|--------------|<br />
| 1 | 220 | 100 |<br />
| 2 | 2000 | 80 |<br />
| 3 | 4000 | 20 |
What I have tried:
I am trying to sum all the columns that have the same product_SKU in a specified date range, but it always gives me duplicated values
select pr.product_sku,
pr.product_name,
pr.brand,
pr.category_name,
pr.subcategory_name,
a.stock_on_hand,
sum(pr.pageviews) as page_views,
sum(acquired_subscriptions) as acquired_subs,
sum(acquired_subscription_value) as asv_value
from dwh.product_reporting pr
join dm_product.product_data_livefeed a
on pr.product_sku = a.product_sku
where pr.fact_day between '2022-05-01' and '2022-05-30'
and pr.pageviews > '0' and pr.acquired_subscription_value > '0'
and store_id = 1
group by pr.product_sku,
pr.product_name,
pr.brand,
pr.category_name,
pr.subcategory_name,
a.stock_on_hand;