365 Days of Daily Coding: Day 35

I feel slightly better today. However, I do feel slight intense pressure on my right shoulder. I have been feeling this way since very long. I guess the accupunture was not that helpful to treat my right shoulder. However, it did help me with my intense neck and back pain. I am just relieved that it was not a stiff neck or anything serious. I would love to go for a back massage but seems unlikely given the rise in the number of cases and the lockdown.

Today’s sqlpad challenge was an interesting one. There were two tables. One table had the number of films played by each actors and the other had a table with their last name and first name. I had to find out the last name and first name of the actor with the highest number of films played.

I built a temporary left join table using WITH that included a new column that counted the number of films played by each actors grouped by actor id. I then selected the first row from the table that was ordered by the count in descending order.

Below is my solution:

WITH temp AS (
  SELECT a.actor_id , a.first_name, a.last_name, COUNT(f.film_id) AS counts
  FROM actor a LEFT JOIN film_actor f
  ON a.actor_id = f.actor_id 
  GROUP BY a.actor_id
  ORDER BY counts DESC
  )
SELECT first_name, last_name FROM temp LIMIT 1;

What’s interesting about this is that multiple actors could have the same last name and first name. In this case, grouping by their last name and first name can be inaccurate and therefore, they have to be grouped by their actor id instead which is unique for each actors.

• Facebook
• Instagram
• LinkedIn
• GitHub