13. Actors' first name

Upgrade and practice this Premium question.

A paid plan gives you access to our coding interview questions, courses on SQL, Python and Machine Learning. As well as AI career copy writing, resume services.

Supercharge My Data Skills Today

***********

***************************************************************************************************
*****************************************
*****************************
****************************************************************************************************************************************************************************************
***************************************************************************

Table: actor

actor_id	first_name	last_name
1	PENELOPE	GUINESS
2	NICK	WAHLBERG
3	ED	CHASE
4	JENNIFER	DAVIS
5	JOHNNY	LOLLOBRIGIDA

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

Sample results

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

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.

Expected results

Your results

More IN, BETWEEN, LIKE, CASE WHEN questions

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