- *************************************************
- **********************************************************
- ************************************************************
Table 1: ad_info
User purchases after clicking on the ad
col_name | col_type -----------------+--------------------- ad_id | bigint user_id | bigint spend | float
Table 2: advertiser
col_name | col_type -----------------+--------- advertiser_id | bigint ad_id | bigint cost | float
Sample results
advertiser_id | roi
---------------+--------------------
1 | 0.123456789
2 | 0.234567890
Solution postgres
WITH advertiser_cost AS (
SELECT advertiser_id, SUM(cost) AS total_cost
FROM advertiser
GROUP BY advertiser_id
),
revenue AS (
SELECT SUM(spend) revenue, A.advertiser_id
FROM ad_info I
INNER JOIN advertiser A
ON A.ad_id = I.ad_id
GROUP BY A.advertiser_id
)
SELECT C.advertiser_id, COALESCE(R.revenue, 0) / C.total_cost AS roi
FROM advertiser_cost C
LEFT JOIN revenue R
ON R.advertiser_id = C.advertiser_id
Explanation
This query is calculating the Return on Investment (ROI) for each advertiser.
The first part of the query creates two temporary tables using the WITH clause. The first table, "advertiser_cost," sums up the total cost for each advertiser by grouping the data by advertiser ID. The second table, "revenue," sums up the revenue for each advertiser by joining the "advertiser" and "ad_info" tables on their respective IDs and grouping the data by advertiser ID.
The second part of the query selects the advertiser ID and ROI for each advertiser. It does this by joining the "advertiser_cost" and "revenue" tables on the advertiser ID and calculating the ROI as revenue divided by total cost. The COALESCE function is used to handle cases where an advertiser has no revenue, setting their ROI to zero instead of dividing by zero.
The left join is used to ensure that all advertisers are included in the final result, even if they have no revenue data.
Expected results