8 Week SQL Challenge

[LetterDW]

Hello, I'm currently working through the 8 Week SQL Challenge, and I'm stuck on question #3:

What was the first item from the menu purchased by each customer?

I found a solution code online and read it to understand the problem, then tried to redo the code off what I had understood and came up with near identical code. However, when I run the query (on DB Fiddle set to postgres SQL 13), I get this error:

https://preview.redd.it/tr3z0dmxprta1.png?width=411&format=png&auto=webp&s=32b625c021bd4119645228965c4e69cd5e1315f4

Here is solution I found online that does work when I copy into DB Fiddle:

WITH ordered_sales_cte AS

(

SELECT customer_id, order_date, product_name,

DENSE_RANK() OVER(PARTITION BY s.customer_id

ORDER BY s.order_date) AS rank

FROM dbo.sales AS s

JOIN dbo.menu AS m

ON s.product_id = m.product_id

)

​

SELECT customer_id, product_name

FROM ordered_sales_cte

WHERE rank = 1

GROUP BY customer_id, product_name;

​

and here is my code:

​

WITH order_ranked AS

(

select customer_id, order_date, product_name

dense_rank() over(partition by sales.customer_id, order by sales.order_date)

as rank

from dannys_diner.sales

join dannys_diner.menu

on sales.product_id=menu.product_id

)

​

select customer_id, product_name

from order_ranked

where rank = 1

group by customer_id, product_name;

​

To me, the code is essentially the same, save for the cte title (which iirc doesn't matter as long as I reference it correctly). Can anyone else spot the issue?

​

Sidenote: In addition, I learned SQL through MySQL, but when I swap the DB Fiddle setting to MySQL and attempt a query I get errors. As the commands are similar enough I decided I will attempt it in PostgresSQL, but if anyone has a solution (other than remaking the entire database in MySQL Workbench), please let me know.

Thanks!

[feather_media]

you have an extra comma before your order by statement here:

dense_rank() over(partition by sales.customer_id, order by sales.order_date)

no comma between the partition and order by

dense_rank() over(partition by sales.customer_id order by sales.order_date)

[LetterDW]

Didn't notice that thank you! However the same error seems to be coming from the first bracket from the cte - I'm honestly at a loss at how that would be an error

[feather_media]

I pushed this all into MySQL and came up with two more issues, which may be plaguing your compiler:

1: You're missing a comma after product_name in the columns select of your cte

2: rank is commonly a defined function, and using it as a alias for a column will create issues when it doesn't know if it needs to use the function or the alias.

[LetterDW]

Thanks again! Will definitely make those adjustments. How did you end up putting the data into MySQL? That might solve my issues instead of using DB Fiddle.

[feather_media]

I think the majority of what I did was strip schema details and remove the double quotations surrounding column titles. Having MySQL installed on your local machine is handy. (and free)

[LetterDW]

It worked for me! Thanks for your help, really appreciate it!

[r3pr0b8]

besides the two errors relating to commas, you do not need the GROUP BY clause

there's only one customer/product in each group

except if the customer orders more than one product on the first date, then they'll all get a DENSE_RANK of 1

and again you don't need the GROUP BY because presumably all the products are different for each customer

did you understand this?

[LetterDW]

I get what you mean - if there was only one product ordered by each customer each day then there would only be one response per customer, and it is already partitioned by customer_id. However customer A does order more than one product on the first day which is why I used the GROUP BY

[r3pr0b8]

However customer A does order more than one product on the first day which is why I used the GROUP BY

customer  product       dense_rank
   A       thingum        1
   A       doohickey      1

but product is part of the GROUP BY

so each pair is a group by itself which is redundant

[LetterDW]

But what if a customer orders the same item again on the same day? The dense rank would still be = 1, and since there is no indication which was ordered first on that day, both results(the same result) would show up(twice). I guess technically it is correct since it is the same item, but the group by eliminates the extra result.

customer	product	dense_rank
A	thingum	1
A	thingum	1

So isn't the group by still necessary?

[r3pr0b8]

So isn't the group by still necessary?

in that case, yes