SELECT total_impressions SUM (impressions) AS device_type FROM campaign, supply_source GROUP BY 1,2,3

SELECT campaign, supply_source, SUM (impressions) AS impressions FROM device_type GROUP BY 1,2

SELECT campaign, supply_source, device_type, SUM (impressions) AS impressions FROM dsp_impressions GROUP BY 1,2,3

The correct answer is: SELECT campaign, supply_source, device_type, SUM (impressions) AS impressions FROM dsp_impressions GROUP BY 1,2,3

Explanation: The correct query to get the total impressions delivered per campaign, per supply source, and per device type is:

SQL

SELECT
    campaign,
    supply_source,
    device_type,
    SUM(impressions) AS impressions
FROM
    dsp_impressions
GROUP BY
    1, 2, 3

✅ Explanation of the Query

This is a standard and efficient SQL query structure for use in Amazon Marketing Cloud (AMC).

1. SELECT campaign, supply_source, device_type, SUM(impressions) AS impressions:
   • It selects the three dimensions (campaign, supply_source, and device_type) that you want to see individually.
   • It uses the aggregation function SUM(impressions) to calculate the total number of impressions for each unique combination of the three dimensions.
   • AS impressions gives the aggregated result column a clear name.
2. FROM dsp_impressions:
   • Impressions for Amazon DSP campaigns are found in the dsp_impressions table in AMC. This table contains the necessary dimensions and the impressions metric.
3. GROUP BY 1, 2, 3:
   • This is the essential step for aggregation. The GROUP BY clause groups all rows that have the same values in the specified columns (campaign, supply_source, device_type).
   • Using the ordinal column numbers (1, 2, 3) is a common and concise way to reference the columns in the SELECT statement in many SQL dialects, including AMC SQL. This ensures the SUM(impressions) calculation is done separately for every unique combination of the three grouping dimensions.

This query meets the requirement of providing total impressions while maintaining granularity by campaign, supply_source, and device_type.