Flexible Calculations in a One-to-Many SQL Relationship

Image of the word SQL being held up by a crane

Background

A Generic Solution

WITH c
AS (
SELECT a.*
,b.*
,Row_number() OVER (
PARTITION BY < join key >
ORDER BY < criteria you want to solve by >
) AS row_number
FROM a
LEFT JOIN b ON a.a_id = b.b_id
)
SELECT c.*
,CASE
WHEN COALESCE(row_number, 1) = 1
THEN < calculated_column >
ELSE 0
END AS dupes_excluded_calculated_column
,< calculated_column > AS dupes_included_calculated_column
FROM c

Production Application

inventory
ad_events
inventory_ad_events
WITH c
AS (
SELECT i.inventory_id
,i.page_url
,i.inventory_count
,e.advertiser_id
,e.VIEW
,Row_number() OVER (
PARTITION BY inventory_id ORDER BY advertiser_id
) AS row_number
FROM inventory i
LEFT JOIN ad_events e ON i.inventory_id = e.inventory_id
)
SELECT inventory_id
,page_url
,advertiser_id
,SUM(VIEW)
,SUM(CASE
WHEN COALESCE(row_number, 1) = 1
THEN inventory_count
ELSE 0
END) AS dupes_excluded_inventory_count
,SUM(inventory_count) AS dupes_included_inventory_count
FROM c
GROUP BY inventory_id, page_url, advertiser_id
new_inventory_with_ad_events
SELECT page_url
,advertiser_id
,sum(dupes_included_inventory_count) AS inventory_count
,sum(VIEW) AS VIEW
FROM new_inventory_with_ad_events
GROUP BY page_url
,advertiser_id
Query Result
SELECT page_url
,sum(dupes_excluded_inventory_count) AS inventory_count
,sum(coalesce(VIEW, 0)) AS VIEW
FROM new_inventory_with_ad_events
GROUP BY page_url
Query Result

Summary

SQL Joke: A SQL query walks into a bar… in one corner there are two tables. The query walks up and asks, mind if i join you?