BYU Student Author: @Kaden_Sandberg
Reviewers: @Abraham_Reyes_Leon
Estimated Time to Solve: 15 Minutes
Need a program? Click here.
Overview
As an analyst at Global Trade Insights, you’ve just learned about Common Table Expressions (CTEs). CTEs are a helpful SQL tool that let you break a complex query into smaller, temporary parts using the WITH clause. Think of a CTE as a temporary table that exists only while your query runs. This makes your SQL code cleaner and easier to work with, especially when you need to filter, aggregate, or join data.
One morning, your boss, Ms. Ramirez, calls you into her office with a new task. The company is preparing its quarterly executive report, and the leadership team needs clear insights into how food imports have been trending over the years.
Ms. Ramirez explains, "We’ve been getting a lot of questions about the growth rate of our food imports. I need you to build a table that summarizes the data by year.
Your table should show:
- Each year
- The total sum of imports for that year
- The year-over-year growth rate
You’ll want to filter for rows where the unit of measure is ‘Million $’ since the data is a little messy. I want you to use a CTE to keep the query organized and easy to maintain." She adds, "This table will not only help us understand our past performance but also guide our future strategy. Can you put together a SQL query that accomplishes this?”
Instructions
Write a SQL query that uses a Common Table Expression (CTE) to summarize food import data by year. Specifically, you should:
-
Filter the data to include only rows where Unit_of_Measure is ‘Million $’.
-
Aggregate the data by Year_Number to calculate the total imports for each year.
-
Calculate the year-over-year growth rate by comparing each year’s total imports to the previous year’s total.
The final output should be a table with three columns:
- Year_Number
- TotalImport (the sum of imports for that year)
- GrowthRate (the percentage growth from the previous year or ((Y2-Y1) / Y1) )
- Post the following in the comments:
- SQL code
- Your answer for which year had the highest growth rate
Do not use Microsoft Access for this challenge. Microsoft Access does not support CTE’s. Instead we recommend you visit slqlite.com. The attached text file contains the SQL code that will write the dataset into the database. Copy it all and run it in the first window by pasting it over the SELECT statement already in the window and clicking the “Run” button in the top left. To run your next SQL code, click the plus sign in the top right (not left!!!) corner. A new tab should appear in the program called “SQLite.1”
Don’t forget to post your solution with your SQL code and highest growth year with its total import sum and growth rate in the comments!
Data Files
Suggestions and Hints
Feel free to leverage GenAI in solving this challenge! If you’ve never used CTE’s before, ask GenAI what they are and how to use them!
Solution