Posted by Ishan, Sept. 25, 2023, 11:08 p.m.
Where exactly am I going wrong in question 41 ?
This is the question that I was attempting...
Question no 41 :
Productive actors vs less-productive actors
Instruction
- Write a query to return the number of
productiveandless-productiveactors. - The order of your results doesn't matter.
Definition
productive: appeared in>= 30films.less-productive: appeared in<30films.
Table 1: actor
col_name | col_type -------------+-------------------------- actor_id | integer first_name | text last_name | text
Table 2: film_actor
Films and their casts
col_name | col_type -------------+-------------------------- actor_id | smallint film_id | smallint
Sample results
actor_category | count -----------------+------- less productive | 123 productive | 456
I tried to solve this question by the code below...
select B.p2,count(B.p2) from
(select
A.a_id a_id2,
A.c_f_id c_f_id2,
(
case
when A.c_f_id>=30 then 'productive'
else 'less-productive'
end
) p2
from
(SELECT fa.actor_id a_id,count(fa.film_id) c_f_id
FROM film_actor fa right join actor a on a.actor_id=fa.actor_id
group by fa.actor_id
order by fa.actor_id)A)B
group by B.p2
;
Th expected output is
Expected results
| actor_category | count |
|---|---|
| less productive | 126 |
| productive | 74 |
My output is coming as :
| p2 | count |
|---|---|
| less-productive | 125 |
| productive | 74 |
I dont know where exactly am I going wrong.
Can anybody please help me ?
