SQL help - trying to select top 10 customers based off order count from order table. Have another rewards table with a Gold member column with either a yes or no if customer is a gold member (cont...)

Posting as :
works at
You are currently posting as works at

Google man and experiment. That’s the best way too learn unless you’re too lazy to use google

like

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

like

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

like

Use RANK on the order count, Join, Case to ensure Reward is NULL when RANK > 10. Finally, WHERE Rank is < 11.

like

Yea...just Google...
Stackoverflow, W3School or all those free online SQL tutorials that walk you through the fundamentals are great resources to learn.

like

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?

like

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.

Related Posts

Can you guyz help me to get 11 like for dm access?
Thanx in advance.🙏🏻

likeuplifting

How to politely tell my partner stop spending too much time on playing video games? It was my false at first. I introduced Zelda breathe of the wild to her and now, she is hooked. She even played game when we had the movie night. I chose the her movie style but she let me to watch it alone and silently turned on the game, and played it while pretended to watch the movie with me. That’s not fun at all. What should I do? Any advice please?

likefunny

Hi Fishes,

I attended hr discussion round on nov 10 and also they approved CTC but still i didn't get offer letter

How long it will take?

like

Hello, requesting information on what all is included in the "Welcome kit"?

What are your plans for paying back your student loans? They feel so crushing right now.

like

New associate (started Oct.) I'm still getting ramped up on workload even though I keep asking for more work. Find myself frequently with nothing to do so leave around 5. Just enjoy while it lasts?

like

Anyone from Deloitte India!
WFH ending soon? Any news on this?

like

Looking for a job in Omaha. Appreciate any leads!

like

I want to open up my own shop but worry about my ability to bring in business. I do securities law I don't want to go the plaintiffs route but really don't want to work for someone else any more. Any advice?

likehelpful

Anyone in this bowl??

like
like

I recently learned from 3 different employees that left (voluntarily) they were making 98-105k. They were outside candidates and were all hired within the last year and for the same position, and same regional territory. I have worked for the company 3+ years and went from a core position, to large loss. I was given a 10k raise putting me around 78k. Yes.... some of these individuals have Lardge Losss experience but does that really make a 20-25k difference? Any advise!?

likehelpful

Friendly reminder: IRA contribution limit increased from $5500 to $6000 in 2019. Make sure to update those automatic contribution amounts!

likesmart

Guys does IBM sends welcome kit to new joinees? If yes, what are the contents of the box?

like

Do you feel like your work is making a difference at your current company? Why or why not?

like

I’d love honest reactions to help ease my mind. I’ve been paying lower rent (like 1400) in HCOL city last few years. Finally debt free from grad school as of this year. Total comp is now low 200k. Does 2800 for rent (im looking at new leases this week) feel high? Moderate? Just want a pulse check on if I’m being reasonable with the 2800, compared to how others think about it. I think it’s ok given HCOL and I now work from home. Disclaimer: this isn’t humble brag stuff. I just want real advice.

like

Belltown or Cap Hill? Moving to Seattle within a couple of months. Any insights regarding foot traffic, rent prices, walkable restaurants/bars?

like

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

like

Folks who left the firm, how much was your PTO payout and how many days had you accrued?

like

I have 4+ years of sales experience. I’m 25 y/o Currently work for a industry leading larger HCM provider. Experience is with Net New Business. I have been a top performer in the country all 4 years. I currently make around 200k OTE. Been closer to 250k due to my performance. I currently feel stuck where I am. Any suggestions on career/company moves? Looking to be able to make 200-300k with at least 120k base. Start-ups, Cybersecurity, etc..

like

Additional Posts in Data & Analytics Consultants

Wanting to get out of fed space. I am a data scientist with 5YOE. What are good companies to look for in DC area that’d hire someone with gov consulting experience?

like

I need a new laptop. Not sure what to look for other than a SSD, 16 GB Ram, and an i7 processor. Fine with Apple or Windows. What else should I look for if I want to do a lot of my own kaggle and

Anyone who has done masters in business analytics Singapore? Could you help on courses, Scholarships and careers and jobs. Looking for help.

Back end or front end?

Post Photo
funnylike

I am finishing up a project and I’m trying to document what I’ve done for my resume. What are some good metrics for someone who mainly created user stories, created dashboards, interacted with the client and developed statistical models? One of my dashboards got deployed to production but it’s to early to see the impact. My other dashboards are almost done but I’m moving on to something else.

like

When you thought you failed the tech screen so did zero prep for later rounds but then get a message and now have less than a week to get your acorns together

Post Photo
likefunny

Anybody looking for data strategy or technical PM for analytics projects?

likehelpful

What’s the coolest data solution you have delivered? The industry is getting a little predictable and some inspiration is always welcome.

like

Analytics 💵 transparency thread!


Format:
Title level (standardized title/job level)
Economic area (Bay Area, Midwest, etc)
Salary + bonus + liquid equity
Anything else you think would help

likesmarthelpful

DM for referral at a boutique consulting firm:

- fully remote
- good WLB
- hiring all levels with above average pay
- 3+ YOE with experience in analytics/BI in financial services background preferred

likehelpful

Does anyone use a 3rd party Excel plug-in like Ablebits?

like

Can someone tell me how log loss values can be used to compare two classification models? I understand the concept of log loss value but I don't quite get how two models can be compared using this metric. Is it as easy as saying, all things equal, one that has a lower log loss value is a better model?

like

Interviewing for a deals analytics role at EY- the interview day will include time with a partner, a senior manager, two managers and a 3 hour case study. What should I expect?

Looking to move from consulting to chill DS/analytics role. Don’t care too much about TC ($150k is fine), but WLB is paramount for me. Anyone have any thoughts as to where I can get this?

like

I’m 3 months into a my first job as an entry level Business analyst with high data focus at a big investment bank but contracted by a consulting firm. I don’t come from a tech/finance background so I was promised training. I don’t feel like I’m don’t feel like I’m learning anything though and everything is so slow placed I feel more like an intern given small tasks. my manager is also new to tech so I don’t know who to look for as a mentor… any advice pls ?

like

Looking for strategy and analytics consulting role in the big 4 . Any help would be welcome thanks

like

What are some technical skills that I should develop to land a Business Intelligence role in the tech industry? SQL and a data vis tool are a must I’m assuming. What else?

like

I am a beginner DA user and trying to gain experience. I am trying to do a project on ESG for fun, and having some difficulty coming up with a hypothesis. Want to pick your brains and see if I can get started. I have data about CO2 emisiones, electricity usage, air quality, and that’s pretty much it. Using alteryx and powerbi (to visualize results) as I don’t have any other programs. Thanks so much!

like

I studied for 2 months with a friend and doubled my income to 200k+ the consulting game is a a racket and to that extent big tech is too BUT big but tech and large streaming companies actually pay top dollar for good talent that can code . These suits have been lying to you ALL. Wake up in the morning and studying my friends you do not need these consulting firms they need you! Do not feed into there bs…studying for interviews and get you fare share of this tech wave… I am making more than my Ms

likefunny

Hi All,
I have a question about replicating an Alteryx model in python. Basically the Alteryx model used to clean, combine, prep, run some business rules and spit out a formatted file ready for running further analysis/viz. Can this same process be automated using a python script? I’m new to python and would like to know what packages to learn to do this.
Thanks!

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal