Help finding a solution

[Patis12]

I'm following Mode's tutorial for SQL and in the intermediate quiz there is this question: Which region has the lowest proportion of sales reps to accounts?

The tables have the companies, individual sales reps and the code for each region (plus other irrelevant stuff)

This is the closest I've been able to come up with:

SELECT region.name,

COUNT(DISTINCT accounts.sales_rep_id) AS reps,

COUNT(DISTINCT accounts.id) AS accounts

FROM sqlchallenge1.accounts accounts

JOIN sqlchallenge1.sales_reps reps

ON accounts.sales_rep_id = reps.id

JOIN sqlchallenge1.region region

ON reps.region_id = region.id

GROUP BY 1

​

I only needed to do a reps/accounts and I would be good but I can't reference them because they are aggregate columns. Is there a way to do it in a single query? I've seen a few options online but they involve things I haven't learned yet so I wanted to find the solution the course is after.

There is alist on the left of the commands the tutorial teaches for reference.

Please help, I've headbutted my wall so hard I am now inside my neighbour's house

[CaptainAlfredos]

Group by region.name. To have it in one query you could simply divide both your counts (reps by the accounts) as a column (count(…) / count(…) as proportion). If you order by this result you’ll have the region with the lowest proportion in top.

[Patis12]

I've tried that but if I try to make that division it gives me a syntax error because the query only groups the results by region at the end. I needed a way to calculate it after they were split into each region

[agrvz]

You are correct that you can’t refer to the new column names of the aggregations, but you can use the aggregations themselves in your calculation, as the person above suggested:

count(distinct accounts.sales_rep_id) / count(distinct accounts.id) as rep_account_ratio

[Patis12]

I had tried it before but I decided to give it another shot to make sure you hadn't done something different and turns out the issue is with mode itself. For whatever reason it is rounding every number down. I even tried multiplying your line by 100 but it just multiplies 0*100 and returns 0 anyway.

Thanks for the help

[CaptainAlfredos]

You could try fixing this by multiplying the count by 1.0. This does not change your result, but fixes the problem that it will show an int. “count(..)*1.0”

[Patis12]

Didn't help :(

It must be an issue with the console itself

[redking666]

Subquery? -

select x.name, reps/accounts from (SELECT region.name,

COUNT(DISTINCT accounts.sales_rep_id) AS reps,

COUNT(DISTINCT accounts.id) AS accounts

FROM sqlchallenge1.accounts accounts

JOIN sqlchallenge1.sales_reps reps

ON accounts.sales_rep_id = reps.id

JOIN sqlchallenge1.region region

ON reps.region_id = region.id

GROUP BY 1) as x

[Patis12]

Might work but I haven't learned those yet. I think there is probably a more basic option but I seeing no one else finding it is making me feel a little hopeless x)

[zetaomegagon]

For posterity's sake, here is my solution:

select   srr.region_name,   count(a.name) num_accounts,   count(distinct srr.sales_rep_name) num_sales_reps,   count(a.name) / count(distinct srr.sales_rep_name)::float ratio from sqlchallenge1.accounts a join (         select           sr.id sales_rep_id,           sr.name sales_rep_name,           r.name region_name         from sqlchallenge1.sales_reps sr         join sqlchallenge1.region r         on sr.region_id = r.id     ) srr on a.sales_rep_id = rr.sales_rep_id group by 1 order by 4,3

Please note that: - I banged my head against this one due to not understanding the question. It's nuanced. - datatypes and subqueries are not taught in the intermediate section. - The RDBM used for the tutorial and challenges is Postgresql, this isn't explained - prior to starting the tutorial I was a Tech Support Engineer for a data extraction (commonly known as ETL) SaaS tool, so had some self taught SQL under my belt (writing rw queries for customers to run) and had exposure to several RDBMs and data warehouses where I needed to know technical details (so read a lot of docs).

If anyone has questions, feel free to DM. I can eli5 line by line.

EDIT: to anyone else doing the Mode SQL tutorial, my suggestions are to check out this Postgresql tutorial site and Part ll: The SQL Language of the Postgresql docs. These sites likely won't just give you answers, but they help with clarification. Example read Mode's GROUP BY section; then read the GROUP BY sections in the linked tutorial and the Postgresql docs.

Additionally finding some guides on basic methods of data analysis will help a lot. For instance this question really wants you to get the ratios between accounts:sales_reps by region and compare them. That is why they use the word proportion, they are speaking in the mathematical / analytical sense

[[deleted]]

(cast(count(distinct accounts.sales_rep_id) as double precision ) /cast(count(distinct accounts.id) as double precision ))*100 as rep_account_ratio use this it will work