Highest grossing items by location, without primary keys

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

Stripe's Markdown framework, Tencent's code analysis tool, and K8s visualization

Thursday, May 19, 2022

StackShare Weekly Email not displaying correctly? View it in your browser. StackShare Weekly Digest May 19th, 2022 Sponsored by LaunchDarkly. Delivering the right features to the right customers can be

Web Tools #461 - Mergify, Frameworks, React Tools, Uncats

Thursday, May 19, 2022

Web Tools Weekly WEB VERSION Issue #461 • May 19, 2022 The following intro is a paid product review for Mergify, a GitHub-based service for automating your pull requests and code merges. If you're

Python Weekly - Issue 550

Thursday, May 19, 2022

View this email in your browser Python Weekly Welcome to issue 550 of Python Weekly. Let's get straight to the links this week. From Our Sponsor Deepnote is a new kind of data notebook that's

Q&A: Deep Dive on Blazor, 3rd-Party Blazor/.NET MAUI Tools, MS Touts IntelliJ IDE for Azure, More

Thursday, May 19, 2022

Researchers Find Potential Way to Run Malware on iPhone Even When it's OFF

Thursday, May 19, 2022

The Hacker News Daily Updates Newsletter cover Linux Cheat Sheet Bundle A free collection of downloadable cheat sheets to help you get the most from Linux. Download Now Sponsored LATEST NEWS May 19,

3D Printing in Higher Education & Research

Thursday, May 19, 2022

Adopt 3D printing in a way that delivers value for your institution View this email in your browser engineering.com Guide - 3D Printing in Higher Education and Research 3D Printing in Higher Education

💭 An Introduction to Content Federation | XS’ Issue #20

Thursday, May 19, 2022

💭 An Introduction to Content Federation | XS' Issue #20 By Esat from Experience Stack • Issue #20 • View online An Introduction to Content Federation When most people think of content management,

wpMail.me issue#563

Thursday, May 19, 2022

wpMail.me wpMail.me issue#563 - The weekly WordPress newsletter. No spam, no nonsense. - May 19, 2022 Is this email not displaying correctly? View it in your browser. News & Articles Does Market

You're invited to our next event - Mobile Development special edition 📱

Thursday, May 19, 2022

In case you've missed it Only 5 days left to join our TED-like online event! This time we will talk about Mobile Development 📱️ Accept Invitation Grab your free ticket Speakers Lineup 🎤 Our events

Google Took My Money and Canceled My Nest Service

Thursday, May 19, 2022

Read in Browser Logo for Review Geek May 19, 2022 I've been a subscriber to Nest Aware for years. I've long touted the Nest Video doorbell as the best premium smart doorbell you can buy. But