365 Days of Daily Coding: Day 55

I worked quite late yesterday and as a result, I was not feeling fresh today. I felt tired the entire day and wasn’t able to achieve much. Never going to work this late again.

Although I am feeling sleepy and my mind is not functioning at its optimum level, I managed to solve a medium challenge in SQLpad. I would have loved to look for alternative way to solve the challenge but I would like to wrap the day.

Medium SQL Challnege:

WITH temp AS
(
  SELECT film_id, inventory_id FROM
  (SELECT f.film_id, i.inventory_id
  FROM inventory i RIGHT JOIN film f
  ON i.film_id = f.film_id) AS k
  )

SELECT in_stock, COUNT(in_stock)
FROM(
SELECT CASE WHEN counts IS NOT NULL THEN 'in stock' 
ELSE 'not in stock'
END AS in_stock
FROM(
SELECT film_id, COUNT(*) AS counts 
FROM temp
WHERE inventory_id IS NOT NULL
GROUP BY film_id
UNION
SELECT film_id, null FROM temp
WHERE inventory_id IS NULL) AS k)
AS l 
GROUP BY in_stock;