C3W3 Adavance SQL

Course Q&A Data Engineering Data Storage and Queries
1

Exercise 1,2,4, 7 why not grade

Screenshot 2568-01-21 at 15.13.40
Screenshot 2568-01-21 at 15.13.40382×614 30.8 KB

Screenshot 2568-01-21 at 15.14.20
Screenshot 2568-01-21 at 15.14.20329×572 29.3 KB

Screenshot 2568-01-21 at 15.15.09
Screenshot 2568-01-21 at 15.15.09650×640 50.4 KB

Screenshot 2568-01-21 at 15.16.45
Screenshot 2568-01-21 at 15.16.45770×597 55.6 KB

Screenshot 2568-01-21 at 15.17.08
Screenshot 2568-01-21 at 15.17.08540×442 31.4 KB

2

Excercise 10,11,12 why error , Please help

Screenshot 2568-01-21 at 15.20.42
Screenshot 2568-01-21 at 15.20.42669×675 34.7 KB

Screenshot 2568-01-21 at 15.21.00
Screenshot 2568-01-21 at 15.21.00553×621 37 KB

Screenshot 2568-01-21 at 15.21.19
Screenshot 2568-01-21 at 15.21.19682×652 40.7 KB

3

Hello @iambenn,

The outputs must have indentical column names:
Exercise 1) the output looks correct, however it is missing the cell tag ex01. Could you right click on the cell and choose notebook cell>add cell tag and type ex01.

Exerccise 2) you need to change the column names from average_by_category to average, floor_average to average_down and ceil_average to average_up

Exercise 4) at the END of the use column name AS value_rate instead for amount_category

Exercise 7) use column name delivery instead of rental_status

4

@iambenn, continue

Exercise 10) you need to use dim_film instead of fact_film_actor and use the correct INNER JOIN:
Use the dim_actor as the main table for this query. Perform the following joins:

  • dim_actor and bridge_actor table on the actor_id.
  • fact_rental and bridge_actor on the rental_id.
  • dim_film and fact_rental on the film_id.

Exercise 11) you need to change the table FROM fact_payment to fact_rental and add customer_id = 388 in the WHERE clause.

Exercise 12) here you use the correct WHERE statements, just need to change the FROM fact_payment to fact_rental same as in Exercise 11

Could you try apply those changes in column names, remove your code and only post outputs if you need any help. Thank you

5

Screenshot 2568-01-22 at 09.35.03
Screenshot 2568-01-22 at 09.35.03662×690 41 KB

Screenshot 2568-01-22 at 09.35.31
Screenshot 2568-01-22 at 09.35.31468×640 39.4 KB

Thank you Georgios , Ex 10,11 error as picture please help

6

Helllo @iambenn,
Exercise 11 has the same structure as Exercise 12, except it uses LAG instead of LEAD and previous_month_amount instead of next_month_amount . To fix the difference column change the order in the subtraction, use (LAG(amount, 1) OVER ( ORDER BY month) - amount

Exercise 10 your screenshot is actually 10.2 things you need to change:

  1. Use FROM dim_actor instead of fact_film_actor ,
  2. INNER JOIN bridge_actor ON bridge_actor.actor_id = dim_actor.actor_id
  3. you have a typo bridege_actor in the second JOIN
  4. keep the last one dim_film is correct

Also in 10.4 (graded) you need to take the query in 10.2 and add things:

  1. name it as actor_film_count
  2. use only GROUP BY actor_id,without ORDER BY and LIMIT
  3. use ROW_NUMBER and create a column actor_rank
  4. finally use a WHERE statement actor_rank <= 10
7

Thank you , I finished

1 Like
8

@iambenn Could you remove the code, cheers

9

yes, Thank you