Hi! I dont understand how to use
EXCEPT on this question. Could you help with it?
Based on my understanding, I got the total number of individual and the total number of ablum are 481 and 135, but I got a total invoice_id 614. How did it happen?
%%sql with ilt as (select t.*,il.* from invoice_line il inner join track t on t.track_id=il.track_id), ailt as (select ilt.invoice_id,a.album_id,count(ilt.track_id) as number_of_track, (case when count(ilt.track_id)<=2 then 'individual' else 'album' end) as category from album a inner join ilt on a.album_id=ilt.album_id group by 1,2) select count(distinct case when category='individual' then invoice_id end) as total_indi_invoice, count(distinct case when category='album' then invoice_id end) as total_ablum_invoice, count(distinct invoice_id) as total_invoice, round(cast(count(distinct case when category='individual' then invoice_id end) as float)/count(distinct invoice_id),2) as percentage_of_indi, round(cast(count(distinct case when category='album' then invoice_id end) as float)/count(distinct invoice_id),2) as percentage_of_album from ailt
What I expected to happen:
What actually happened:
total_indi_invoice total_ablum_invoice total_invoice percentage_of_indi percentage_of_album 481 135 614 0.78 0.22