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

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

Home | News | How To | Webcasts | Whitepapers | Advertise .net insight May 19, 2022 THIS ISSUE SPONSORED BY: Free version of 'Learning Blazor' eBook by David Pine Developer Tools to Prevent Bad

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