Related Posts
Anyone in this bowl??
Johnson & Johnson Mondelēz International I am working as a Territory Manager with Cars24 from around 11 months and in total of around 6 years of working experience as a Team Leader. Data Management # Channel Sales # General & Modern Trade # FMCG # New Product Launch # Marketing & Finance. Looking out for a organisational and cultural change to enhance my productivity and learning.🙏Britannia Industries Limited ITC Limited Johnson & Johnson Nestle Dabur India Colgate-Palmolive Pepsico Marico Hindustan Unilver Limited Mondelēz Internatio
Additional Posts in Data & Analytics Consultants
Back end or front end?
New to Fishbowl?
unlock all discussions on Fishbowl.
Google man and experiment. That’s the best way too learn unless you’re too lazy to use google
Or you can take one of the cheap $10 courses on UDEMY but even I haven’t bothered to do that cos most times, ain’t got time for that. Google is your best friend here
subquery would work if you’re not worried about performance. Temp table if you are. Do your join and then use a case statement for the gold member column. Pseudo code:
Case when customer_id not in (select top 10 customer_id from Customer order by order_count DESC;) then null else gold_member as gold_member
Use RANK on the order count, Join, Case to ensure Reward is NULL when RANK > 10. Finally, WHERE Rank is < 11.
Yea...just Google...
Stackoverflow, W3School or all those free online SQL tutorials that walk you through the fundamentals are great resources to learn.
How can I join the two tables and also have the Rewards column become Null if they are not a top 10 customer. What I have so far is: SELECT TOP 10 customer.number, COUNT(customer.order_count) as Order_Count,rewards.gold_member
FROM Customer
LEFT JOIN number ON customer.number=rewards.number
Not sure how to make sure I only select the top 10 and already the rewards column around to be null if not top 10
Can you rephrase your ask more clearly? You want the top 10 rewards customers by order count? I’m confused on what you want in the rewards column
Sorry about the confusion, I'm looking for the top 10 customers by order count and also want to replace any values in the Gold member column with a Null if they aren't a top 10 customer. So essentially I'll have a list of all the customers from the Reward table and join that with the top 10 customers from the Customer table. I'm assuming I'd do a left join to keep the total population of customers. For all the customer IDs I don't have a match for after doing the left join with the top 10 customers query, id have a null in the Gold member column. For all those where I do have a match with the top 10 customer query, id keep the text that already exists in the Gold member column
Thanks all this is all great, super helpful! Just general question, when do you prefer using order by vs rank?
Its not either or. When you use RANK, you need ORDER BY. Otherwise, what you even ranking based on?
Thanks all! Really need to take a SQL crash course. Feel like I'd get more out of it in person. Any recommendations for SQL courses? If you know of a great online one I'm willing to check it out too! In Chicago if that matters!
This is a question on Leetcode, free Medium.
There is a clear solution also.
Pretty popular so look that up.
There are only 2 free hard solutions, one of those sub part has this with rank order and optimized solution something.