FAQ: Code Challenge: Aggregate Functions - Code Challenge 4

Frequently Asked Questions SQL FAQ
1

Community%20FAQs%20on%20Codecademy%20Exercises

This community-built FAQ covers the “Code Challenge 4” exercise from the lesson “Code Challenge: Aggregate Functions”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development
Data Science

FAQs on the exercise Code Challenge 4

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

2

to the curriculum devs:
Give a couple of hints at least before exposing the whole answer.

4 Likes
4

SPOILER: SOLUTION SHOWN IN ANSWER

Is the chart diagram (shown below) missing something?

I say this because when we are only looking at the columns user_id and amount we see that only 2 distinct values display under amount 24.99 and 12.99. (I will use user_189 as an example thoughout this question)

If this is true how is possible that the sum grouped by user is anything other than 12.99 or 24.99?

In other words, what exactly are we adding together if there is only one column containing value?
Does it actually look something like this??
image

Even when I include the pay_date I can see that only one payment shows up for this user.

Any ideas what’s going on here? Where are the other payments???

EDIT: It looks like I can only include one photo as a new user. Hopefully you can still piece together what I’m talking about.

PS Only one photo? That’s kind of lame

5

Hello, @micro0794173399.

Welcome to the forums.

I’m not sure I quite understand what you are asking. I queried the payments table first for the sum of payments for only the user with user_id 189. I also queried the payments table for all columns where user_id is 189. These are the results:
image
User #189 made a total of 9 payments totaling $152.91.

6

Ahh curious, I can’t see what I has typed in previously but I must have had something that filtered out those results by mistake!

Thank you so much!! I really appreciate it!

1 Like
7

I made a query:

WHERE amount > 0

instead of

WHERE status = ‘paid’

Logically if more than zero then it means there was a payment?. What would be a disadvantages to do it that way?.

The one I can think of now is by using aggregate (bigger than zero) with WHERE function which may make higher impact on performance?. Is “bigger than” counted as aggregate?

1 Like
8

I didnt think the where statement was case sensitive?

I am getting no results when I run:
SELECT user_id, SUM(amount)
FROM payments
WHERE status = ‘PAID’
GROUP BY 1
ORDER BY 2 desc;

But i get the correct answer when i run:
SELECT user_id, SUM(amount)
FROM payments
WHERE status = ‘paid’
GROUP BY 1
ORDER BY 2 desc;

9

If SUM is an aggregate function, and “HAVING” must be used with aggregate functions, why do we use “WHERE” in the four codes below?

–

SELECT user_id, SUM(amount)

FROM payments

WHERE status = ‘paid’

GROUP BY 1

ORDER BY 2 DESC;

–

SELECT pay_date, SUM(amount)

FROM payments

WHERE status = ‘paid’

GROUP BY pay_date

ORDER BY SUM(amount) DESC;

–

SELECT AVG(amount)

FROM payments

WHERE status = ‘paid’;

–

SELECT medium, COUNT(*)

FROM met

WHERE medium LIKE ‘%gold%’ OR medium LIKE ‘%silver%’

GROUP BY 1

ORDER BY 2 DESC;

10

Hello @purplecodess !

I believe HAVING doesn’t have to be used with all aggregate functions, only with the GROUP BY aggregate function. That’s why HAVING is always used right after GROUP BY.

I hope this helps :slight_smile:

11

it’s pretty frustrating when you’ve done it all correctly but it’s “wrong” due to you displaying too many columns or something. it’s not the end of the world but it’s a bit silly

1 Like
12

VALID faq-code-challenge-aggregate-functions-code-challenge-4 Thx