Highest grossing items by location, without primary keys

InterviewQs

Highest grossing items by location, without primary keys

SQL Database Querying Subquery Window Function

Forward this email to a friend or colleague and challenge them to solve it.

Hi, 

Suppose you’re given the following tables, showing item prices by location as well as item sales by location:

Table: item_prices_by_location

city item_name price
New_York Coat 115
New_York Hat 40
New_York Shirt 65
New_York Pants 90
Los_Angeles Coat 110
Los_Angeles Hat 35
Los_Angeles Shirt 60
Los_Angeles Pants 85
Austin Coat 105
Austin Hat 30
Austin Shirt 55
Austin Pants 80
London Coat 115
London Hat 40
London Shirt 65
London Pants 90

Table: item_sales_by_location

city item_name num_sales
New_York Coat 1055
New_York Hat 900
New_York Shirt 1400
New_York Pants 1000
Los_Angeles Coat 500
Los_Angeles Hat 450
Los_Angeles Shirt 1200
Los_Angeles Pants 800
Austin Coat 300
Austin Hat 200
Austin Shirt 1600
Austin Pants 1000
London Coat 1400
London Hat 700
London Shirt 1200
London Pants 1300

Using these tables, write a SQL query to return total revenue by location by item (num_sales*price), and add a column that ranks each item within a given city based on total revenue in descending order. In other words, we want to see which items in each city drive the most revenue.

Note/hint: you’ll notice these tables do not offer a set of primary keys at the location/city level, so you’ll have to come up with a way to generate your own primary key to join them together


Click here to view/query the tables in an interactive SQL fiddle.


 

You’ve accumulated 289 questions since joining our email list. Create a premium account to access detailed solutions to each problem (including this one) + our new Case studies.


Was this email forwarded to you? You can join our list here to receive questions

How did you like today's question?

15411 Boischatel, Montreal, QC, H9H1Y7, Canada
Don't want to get these emails anymore? We'll forgive you if you unsubscribe

Older messages

Calculating moving averages for stock prices, visualizing

Wednesday, May 4, 2022

Python, Pandas, Moving Average, Data Structures, InterviewQs Calculating moving averages for stock prices, visualizing Python Pandas Moving Average Data Structures Forward this email to a friend or

Writing a simple linear regression function

Monday, May 2, 2022

Python, Arrays, Linear Regression, User Input, InterviewQs Writing a simple linear regression function Python Arrays Linear Regression User Input Forward this email to a friend or colleague and

Bayesian analysis of Price is Right showcase

Friday, April 29, 2022

Python, Statistics, Bayes' Theorem, Modeling, Data Visualization, Baysian Inference, InterviewQs Bayesian analysis of Price is Right showcase Python Statistics Bayes' Theorem Modeling Data

Profit margins for all combinations of sales channels by org

Wednesday, April 27, 2022

SQL, Database Querying, Subquery, Cross Join, InterviewQs Profit margins for all combinations of sales channels by org SQL Database Querying Subquery Cross Join Forward this email to a friend or

Damage due to natural disasters

Monday, April 25, 2022

Python, Data Structures, External Dataset, Data Visualization, Data Processing, InterviewQs Damage due to natural disasters Python Data Structures External Dataset Data Visualization Data Processing

You Might Also Like

Issue 310 - New Autopark looks awesome!

Thursday, March 28, 2024

View this email in your browser If you are just now finding out about Tesletter, you can subscribe here! If you already know Tesletter and want to support us, check out our Patreon page Issue 310 - New

Programmer Weekly - Issue 199

Thursday, March 28, 2024

View this email in your browser Programmer Weekly Welcome to issue 199 of Programmer Weekly. Let's get straight to the links this week. Quote of the Week "Optimization hinders evolution.

wpmail.me issue#660

Thursday, March 28, 2024

wpMail.me wpmail.me issue#660 - The weekly WordPress newsletter. No spam, no nonsense. - March 27, 2024 Is this email not displaying correctly? View it in your browser. News & Articles What's

New attack targets Apple devices

Thursday, March 28, 2024

Eufy's new Mach S1 Pro; Using VR in a car; April solar eclipse FAQ -- ZDNET ZDNET Tech Today - US March 28, 2024 placeholder New password reset attack targets Apple device users - what to do if it

Web Tools #558 - ImageKit Review, JS Libraries, Git/CLI Tools, Jamstack

Thursday, March 28, 2024

WEB VERSION Issue #558 • March 28, 2024 The following is a paid product review for ImageKit's Video API, a developer-friendly toolkit for real-time video optimizations and transformations, to help

An Emmy-winner's guide to AI video

Thursday, March 28, 2024

They built this in 2 months 👀 ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

wpmail.me issue#660

Thursday, March 28, 2024

wpMail.me wpmail.me issue#660 - The weekly WordPress newsletter. No spam, no nonsense. - March 27, 2024 Is this email not displaying correctly? View it in your browser. News & Articles What's

Amazon writes Anthropic a $2.75B check

Thursday, March 28, 2024

Amazon has completed its promised $4B investment in the AI company View this email online in your browser By Alex Wilhelm Thursday, March 28, 2024 Welcome to TechCrunch AM! Today we have a giga-round

Airtrain, Pretzel, SpinKube, Glide, GPTScript, and more

Thursday, March 28, 2024

StackShare Weekly Email not displaying correctly? View it in your browser. StackShare Weekly Digest March 28th, 2024 Stop manually typing out what technologies are being used in your repos in README

Web Tools #558 - ImageKit Review, JS Libraries, Git/CLI Tools, Jamstack

Thursday, March 28, 2024

WEB VERSION Issue #558 • March 28, 2024 The following is a paid product review for ImageKit's Video API, a developer-friendly toolkit for real-time video optimizations and transformations, to help