Shopify SQL Interview Question for Data Scientists and Data Analysts (StrataScratch 2118)

Solution and walkthrough of a real SQL interview question for Data Scientist and Data Analyst technical coding interviews. This question was asked by Shopify and is called "Most Sold in Germany".
Try this question on StrataScratch: platform.strat...
Find me on LinkedIn: / frederikmueller
Playlists:
StrataScratch SQL Coding Questions: • StrataScratch Coding Q...
LeetCode database SQL problems: • LeetCode Database Prob...
LeetCode easy SQL problems: • LeetCode Easy Database...
LeetCode medium SQL problems: • LeetCode Medium Databa...
LeetCode hard SQL problems: • LeetCode Hard Database...

[@frederikmuller]

Try this question yourself: platform.stratascratch.com/coding/2118-most-sold-in-germany?via=frederik

[@itsplaylab4376]

You are great in explaining the solutions 👍

[@ranjan_akash]

Awesome 👌

[@TheRaju991]

Thanks!

[@Phoenix231092]

ok I'm a bit confused, when you group by market as well - would that not give the most sold products per market (the count of order_id would also happen within ProdId/Market group)
I assumed the question wanted the product across all of germany , regardless of markets.

[@frederikmuller]

actually, the market name here refers to the name of the product when displayed on the market. If you look at the final output it will be something like Samsung Galaxy Tab A. That's why there's also only one market name per product_id and grouping by both shouldn't change anything.

[@glstnlev]

Why doing a distinct count of order it though? and not just count

[@frederikmuller]

handles duplicates just in case there are any

[@hasanmougharbel8030]

Hey there,keep up the great work in your channel.
I have only a general enquiry as a newbie sql learner.
Could i channel shopify live data into an sql application?(This should be an application developed for a company of 50 employees mainly for the sake of generating reports and analytics))
Is this an easy process to undertake as a sql beginner? What should i keep in mind
and what are the tools or softwares that i should work with right from the beginning.
For notice, I made my mind to work with microsoft sql server, also our company has a dedicated server.
Thanks for taking care of my enquiry.

[@frederikmuller]

I’m not the right person to ask this, you could ask a Data Engineer or look into connecting your Shopify data to Azure/AWS/Google Cloud storage.

[@frederikmuller]

My solution:
SELECT market_name FROM
(SELECT product_id, market_name, COUNT(DISTINCT shopify_orders.order_id), RANK() OVER (ORDER BY COUNT(DISTINCT shopify_orders.order_id) DESC) AS r
FROM shopify_orders
JOIN shopify_users ON shopify_orders.user_id = shopify_users.id
JOIN map_product_order ON shopify_orders.order_id = map_product_order.order_id
JOIN dim_product ON map_product_order.product_id = dim_product.prod_sku_id
WHERE shopify_users.country = 'Germany'
GROUP BY product_id, market_name) german_orders_ranked
WHERE r = 1

[@chillvibe4745]

I got almost the same solution except I did COUNT(*) instead of COUNT DISTINCT.
Everything else is the same.

[@sunitasah90125]

Main Bharat se hun tum kahan se ho