42. Films that are in stock vs not in stock

medium

Instruction

  • Write a query to return the number of films that we have inventory vs no inventory.
  • A film can have multiple inventory ids
  • Each film dvd copy has a unique inventory ids

Table 1: film

       col_name       |  col_type
----------------------+--------------------------
 film_id              | integer
 title                | text
 description          | text
 release_year         | integer
 language_id          | smallint
 original_language_id | smallint
 rental_duration      | smallint
 rental_rate          | numeric
 length               | smallint
 replacement_cost     | numeric
 rating               | text

Table 2: inventory

Each row is unique, inventoy_id is the primary key of this table.

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint

Sample results

   in_stock   | count
--------------+-------
 in stock     |   123
 not in stock |   456

Solution postgres

SELECT in_stock, COUNT(*) 
FROM (
	SELECT 
		F.film_id, 
		MAX(CASE WHEN I.inventory_id IS NULL THEN 'not in stock' ELSE 'in stock' END) in_stock
	FROM film F
	LEFT JOIN INVENTORY I
	ON F.film_id =I.film_id
	GROUP BY F.film_id
) X
GROUP BY in_stock;
    

Explanation

This query is retrieving the count of movies that are "in stock" and "not in stock" from a database table. The query begins by selecting the "in_stock" column and the count of all records.

Then, it uses a subquery that joins the "film" and "inventory" tables based on the "film_id" column. The subquery groups all the records by "film_id" and selects the maximum value of the "in_stock" column based on whether or not the "inventory_id" is null. If the inventory_id is null, then the movie is "not in stock", otherwise it is "in stock".

The outer query groups the results of the subquery by the "in_stock" column, which groups all the movies that are "in stock" and all the movies that are "not in stock" together. Finally, it counts the number of movies in each group and returns the result.

Expected results



More OUTER JOIN questions

ID Title Level FTPR
194 Daily active users report for new market snap easy
20%
191 Number of replies by group twitter medium
18%
172 Comments distribution facebook hard
13%
168 Average number of streams in the US tiktok medium
13%
140 Daily sales of restaurant 100011 doordash hard
8%
118 Daily bookings in the US airbnb hard
5%
100 Advertiser ROI facebook medium
8%
56 Total number of actors easy
30%
55 Busy days and slow days medium
10%
54 Customer groups by movie rental spend medium
20%
53 Movie groups by rental income hard
11%
52 Movies cast by movie only actors easy
11%
51 Movie only actor easy
37%
45 Movie inventory optimization hard
9%
44 In-demand vs not-in-demand movies medium
6%
43 Customers who rented vs. those who did not medium
8%
41 Productive actors vs less-productive actors hard
7%