has anyone figured out order by for question 15?

[giffiecode]

i've tried order by revenue DESC, film.film_title and order by revenue DESC, film.film_id and order by revenue DESC, film.release_year but none is working correctly

[eoinoconnell04]

You may have figured this out by now, but I was able to get mine to pass by using ORDER BY revenue DESC. It seems that the expected output is underdetermined and that the order of the titles for the same revenue value seems to be in whatever order that Postgres determined would be most efficient.

[giffiecode]

@eoinoconnell04 Thanks

after trying order by revenue DESC, I realized my revenue is off but i can't tell what's incorrect. Does anyone know what's incorrect with the code below?

SELECT
    f.title,
    COALESCE(SUM(p.amount), 0.00) as revenue
FROM
    film f
left JOIN
    inventory i ON f.film_id = i.film_id
left join
    rental r ON i.inventory_id = r.inventory_id
left join
    payment p ON r.rental_id = p.rental_id
left join
    customer c ON r.customer_id = c.customer_id
GROUP BY
    f.film_id,
    f.title
ORDER BY
    revenue DESC;

[eoinoconnell04]

@ypei23

If you remove the group by for f.film_id, and only group by f.title then I believe your code produces the output in the correct order.

[echen4628]

@eoinoconnell04 I'm also getting that the ordering of all the films with revenue 0 seems to be undefined. Were you able to pass the test cases?

EDIT: actually my issue was because i did coalesce with 0 instead of 0.00. I can pass the test case now.

[giffiecode]

@eoinoconnell04 wooo that works. thanks!!!