5. Which 3 marketing campaigns average the highest cost?

Overview: 

Lets break down this down into sub-questions: 

  1. How do I calculate the average cost per marketing campaign? 
  2. How do I sort the results so that the highest values are ranked first? 
  3. How to I pull only the top 3 results? 

Answers to questions: 

  1. To calculate the average cost per campaign, first define the dimensionality of the result by dragging the Description field from the marketing table into the Dimensions box of the chart creator. Next, drag the Cost field from the marketing table into the Measures box and select the aggregation function: Average (the average function sums the values of the declared column and divides by the count of rows over which it is being applied). 
  2. Order the results by sorting the Average Cost field descending in the Measures section of the Chart Creator
  3. Return only the top 3 results by applying a LIMIT of 3. 

Data Location:

Analysis Technique: 

SQL Concepts:

Data Transformations:

  • None required, though the sort and limit could be applied using the data pipeline

Visualization Approach:

Interactive Mode Configuration and Sample Answer:

*Sample Answer not meant for exact comparison, results may change over time

Sorting applied via chart creator, shown below:

SQL

SELECT 
      "Marketing"."description" AS "Description"
    , AVG("Marketing"."cost") AS "Average Cost"
FROM "public"."marketing" AS "Marketing"
GROUP BY "Description"
ORDER BY "Average Cost" DESC, "Description" ASC 
LIMIT 3;