***********
- **************************************
************************************** - *****************************************
**********
************************>= 30************************************<30*******
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
Solution postgres
SELECT actor_category,
COUNT(*)
FROM (
SELECT
A.actor_id,
CASE WHEN COUNT(DISTINCT FA.film_id) >= 30 THEN 'productive' ELSE 'less productive' END AS actor_category
FROM actor A
LEFT JOIN film_actor FA
ON FA.actor_id = A.actor_id
GROUP BY A.actor_id
) X
GROUP BY actor_category;
Explanation
This query is used to group actors into two categories based on their productivity. The first category is "productive," which includes actors who have acted in 30 or more films. The second category is "less productive," which includes actors who have acted in less than 30 films.
The query selects the actor_category and the count of actors in each category.
To achieve this, the query first selects the actor_id and uses a CASE statement to categorize them as either "productive" or "less productive." It counts the number of distinct film_ids each actor has acted in, and if the count is greater than or equal to 30, the actor is categorized as "productive."
The subquery is then grouped by actor_id, and the outer query groups the actor_category and counts the number of actors in each category.
Overall, this query provides insights into the productivity of actors and can help in making casting decisions or identifying successful actors in the film industry.
Expected results