SELECT supply_source, SUM (total_cost)/100000000 AS total_cost_dollars, ((SUM (total_cost)/100000000)/SUM (impressions)) *1000 AS avg_cpm, SUM (impressions) AS impressions FROM dsp_inventory GROUP BY 1

SELECT supply_source, SUM (total_cost)/100000 AS total_cost_dollars, ((SUM (total_cost)/100000)/SUM (impressions)) *1000 AS avg_cpm, SUM (impressions) AS impressions FROM dsp_impressions GROUP BY 1,2

SELECT supply_source, SUM (total_cost)/100000 AS total_cost_dollars, ((SUM (total_cost)/100000)/SUM (impressions)) *1000 AS avg_cpm, SUM (impressions) AS impressions FROM dsp_impressions GROUP BY 1

The correct answer is: SELECT supply_source, SUM (total_cost)/100000 AS total_cost_dollars, ((SUM (total_cost)/100000)/SUM (impressions)) *1000 AS avg_cpm, SUM (impressions) AS impressions FROM dsp_impressions GROUP BY 1

Explanation: That’s the correct query. Here is an explanation of the adaptation and why it works:

🛠️ Adapted Query for Cost and Impressions by supply_source

The adapted query focuses on aggregating the desired metrics at the supply_source level, utilizing fields available in the dsp_impressions table:

SQL

SELECT
    supply_source,
    SUM(total_cost) / 100000 AS total_cost_dollars,
    ((SUM(total_cost) / 100000) / SUM(impressions)) * 1000 AS avg_cpm,
    SUM(impressions) AS impressions
FROM
    dsp_impressions
GROUP BY
    1

---

🔍 Explanation of Changes

The core principle of the adaptation is to replace the previous grouping dimensions with the single dimension required: supply_source.

1. New Grouping Dimension

• Change: GROUP BY 1
• Reasoning: Since the goal is to break down the metrics only by supply_source, we remove campaign and device_type from the SELECT and GROUP BY clauses. Grouping by the first column selected (supply_source) aggregates the data to the correct level.

2. Including Cost and CPM Metrics

Reasoning: CPM is calculated as (Total Cost / Total Impressions) $\times 1000$. The query uses the already calculated total cost in dollars (or the scaled unit) and divides it by the total impressions, then multiplies by 1,000 to get the cost per thousand.

Metric:total_cost

Calculation: SUM(total_cost) / 100000 AS total_cost_dollars

Reasoning: The total_cost field in the dsp_impressions table is typically reported in microcents (a standard Amazon Ads unit). To convert this to dollars (or the local currency), you must divide by 100,000,000 (microcents in a dollar) or use the specific conversion factor provided by Amazon for certain cost fields. In the context of the provided answer’s calculation, the factor of 100,000 implies the cost metric is stored in a unit where 1 dollar equals 100,000 units (which is typical for a cost-per-10-impressions or a similar internal metric in some AMC templates, often simplified to represent cost in dollars when divided by 100,000). We adhere to the correct answer’s division by 100000 for this specific context.

Metric:impressions

Calculation: SUM(impressions) AS impressions

Reasoning: This is the standard aggregation for the total number of impressions.

Metric:avg_cpm (Average Cost Per Mille / Thousand Impressions)

Calculation: ((SUM(total_cost) / 100000) / SUM(impressions)) * 1000 AS avg_cpm