SQLPad Forum question: #58

#58

Hello again :)

MYSQL solution for #58:

SELECT i.film_id,
       (sum(p.amount)/(select sum(amount) from payment))*100 as revenue_percentage
FROM inventory i
inner join rental r
   on i.inventory_id = r.inventory_id
inner join payment p
   on r.rental_id = p.rental_id
where i.film_id<=10
group by i.film_id
order by i.film_id;

Anything wrong with this solution?

This question is closed

Answers

Comment

1. The query looks okay, even though we are asking to use a window function style solution.

2. The subquery in the denominator is very inefficient though, cuz every row you will have to re-compute the sum from the payment table.

3. Also you will want to multiply by 100.0 instead of 100 to improve the accuracy.

Would recommend changing

(sum(p.amount)/(select sum(amount) from payment))*100

(sum(p.amount) * 100.0/(select sum(amount) from payment))

4. Lastly (I am nitpicking here : )), the coding style makes it really hard to read your code, would recommend you consider making your coding style consistent (e.g., using upper case for all keywords.)

Hope it helps,

Leon

Leon (949)

March 23, 2022, 3:51 p.m.