Lets break down this down into sub-questions:
- How do I calculate the average cost per marketing campaign?
- How do I sort the results so that the highest values are ranked first?
- How to I pull only the top 3 results?
Answers to questions:
- 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).
- Order the results by sorting the Average Cost field descending in the Measures section of the Chart Creator
- Return only the top 3 results by applying a LIMIT of 3.
- None required, though the sort and limit could be applied using the data pipeline
Interactive Mode Configuration and Sample Answer:
Sorting applied via chart creator, shown below:
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;