13. Actors' first name

medium

Instruction

  • Write a query to return the number of actors whose first name starts with 'A', 'B', 'C', or others.
  • The order of your results doesn't matter.
  • You need to return 2 columns:
  • The first column is the group of actors based on the first letter of their first_name, use the following: 'a_actors', 'b_actors', 'c_actors', 'other_actors' to represent their groups.
  • Second column is the number of actors whose first name matches the pattern.

Table: actor

  col_name   | col_type
-------------+--------------------------
 actor_id    | integer
 first_name  | text
 last_name   | text

Sample results

actor_category | count
----------------+-------
 a_actors       |    13
 b_actors       |     8

Expected results

Solution postgres

SELECT  
 CASE WHEN first_name LIKE 'A%' THEN 'a_actors'
      WHEN first_name LIKE 'B%' THEN 'b_actors'
      WHEN first_name LIKE 'C%' THEN 'c_actors'
      ELSE 'other_actors' 
      END AS actor_category,
  COUNT(*)
FROM actor
GROUP BY actor_category;
    

Explanation

This query is counting the number of actors in the "actor" table and grouping them into categories based on the first letter of their first name. If their first name starts with "A", they are classified as "a_actors", if it starts with "B", they are classified as "b_actors", if it starts with "C", they are classified as "c_actors", and all other actors are classified as "other_actors". The query then counts the number of actors in each category and returns the result.



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
36%
177 Purchases by platform report amazon medium
9%
176 Employees' annual bonus amazon easy
11%
160 Sellers with no sales by day ebay hard
7%
156 Cancellation rate by unbanned users lyft hard
14%
155 Driver with the highest cancellation rate lyft easy
22%
151 Salary report dropbox easy
16%
138 Happy restaurants doordash easy
10%
136 Extremely late orders doordash easy
5%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration mobile hard
12%
35 Film length report easy
26%
34 Stocked up movies easy
28%
33 Returning customers medium
8%
32 Unpopular movies hard
16%
31 Movies that have not been returned easy
27%
30 Inactive customers in May easy
21%
29 Second highest spend customer medium
16%
28 Film with the second largest cast medium
25%
27 Film with the largest cast easy
27%
26 Second shortest film easy
29%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
20%
22 Average cost per rental transaction easy
43%
15 Fast movie watchers vs slow watchers hard
5%
14 Good days and bad days hard
4%
12 Actors' last name ending in 'EN' or 'RY' easy
30%
11 Actors' last name easy
24%