Query 3 inferred customer preference from an existing product attribute — film length — which was already sitting in the schema, ready to use. Query 4 builds on that idea in two critical ways, further enhancing the database’s limited informational scope:
Annotated Schema
Tables used in this query are highlighted. Others are shown for context.
Reasoning
These films don’t exist outside this database — the descriptions are synthetic text, with no real movies, IMDb pages, or external sources to check against. Whatever can be determined about a film’s protagonist has to come entirely from film.description itself. Query 3 categorizes films by an attribute the schema already provides; Query 4 builds the attribute from scratch before any categorization is possible. Reading a sample of these descriptions directly — not running code against them, but reading them with my own eyes — surfaced a consistent pattern: every description follows a fixed generation template, structured around the word “who.” A query against the full catalog confirms the pattern holds without exception. Exploiting that pattern, a description only counts as having a female protagonist if “Woman” or “Girl” appears before “who,” since that’s the position a grammatical subject occupies in this structure.
The parsing process required checking in two directions, not just one. First: examine every description that matched “woman” or “girl” to catch false positives — cases where the word appeared but didn’t actually signal a female protagonist. This is where “Womanizer” turned up, matching the raw substring search. While “womanizer” is usually thought to be a man, it could in fact be a woman. But the key here is that there is a possibility that it’s a man, and a term only qualifies if it exclusively implies female. Word-boundary padding (' Woman ' with leading and trailing spaces) fixed it.
Second, and less obvious: examine descriptions that didn’t match “woman” or “girl” at all, to check for false negatives — protagonists who were female without either of those specific words appearing. This meant reading raw descriptions directly, looking for other language that might signal “female” just as reliably. “Feminist” came up this way — briefly considered as a third search term, then rejected on the same logic: a feminist isn’t necessarily a woman, so the term couldn’t reliably signal one.
After the list of female-protagonist films is established, defining a “preference” for renting such films requires defining a measurable baseline. Each customer’s personal ratio of female-protagonist rentals to total rentals is compared against a catalog-wide ratio: the count of qualifying films divided by the count of all films in the catalog. That catalog-wide ratio deliberately uses a count of unique films as the denominator, not inventory copies, so multiple copies of films don’t skew the baseline.
The second half of the query asks a separate question: does this preference say anything about how much a customer spends? Or conversely, does how much a customer spends say anything about their preference for female-protagonist films? Every customer — not just the Female Protagonist Positive ones — is ranked into revenue quartiles via NTILE(4) and ordered by SUM(p.amount) descending. That result is then pivoted, using LAG() to place the “Female Protagonist Positive” total alongside the “No Preference” total within each quartile, so the two groups can be compared side by side.
/*
Query 4 - Query used for Slide 4
Question: Identify customers with a preference for films with a female protagonist ("Female Protagonist Positive"). Next, divide the customer base (including all customers, not just ones identified as Female Protagonist Positive) into quartiles, based on each customer's total rental payments made ("Customer Rental Revenue"). Within each quartile, how much total Customer Rental Revenue is represented by customers identified as Female Protagonist Positive versus all other customers?
*/
/* table1 is a list of every film with " Woman " or " Girl " in the film's description, before the word "who". This means the film has at least one female protagonist. If the only occurrence of " Woman " or " Girl " in the description is after the word "who", the film has been excluded, since the " Woman " or " Girl " is not the protagonist (e.g. inventory_id 115). Leading and trailing spaces have been included in " Woman " and " Girl " to exclude any occurrences of those words within other words (e.g. "Womanizer" is the protagonist in inventory_id 260). Note, while a womanizer could be a woman, we don't know they are female for certain, in the same way that a scientist could be female but we don't know for certain, one way or the other, and will thus not include "scientist" in the list. Similarly, "Feminist" might not necessarily be a female, so we are not including "Feminist" in the list). */
WITH table1 AS (
SELECT f.film_id
FROM film f
WHERE (
STRPOS (f.description, ' Woman ') > 0 AND
STRPOS (f.description, ' Woman ') < STRPOS (f.description, ' who ')
) OR
(
STRPOS (f.description, ' Girl ') > 0 AND
STRPOS (f.description, ' Girl ') < STRPOS (f.description, ' who ')
)
),
/* table2 is a list of customer_id's of customers who have rented films with a female protagonist and how many times they have rented such films. */
table2 AS (
SELECT c.customer_id,
COUNT (r.rental_id) AS female_protagonist_rental_count
FROM rental r
JOIN customer c
ON r.customer_id = c.customer_id
JOIN inventory i
ON i.inventory_id = r.inventory_id
JOIN film f
ON f.film_id = i.film_id
WHERE i.film_id IN (
SELECT *
FROM table1
)
GROUP BY 1
ORDER BY 1, 2
),
/* table3 is a list of all customer_id's and how many total films each has rented. */
table3 AS (
SELECT c.customer_id,
COUNT (r.rental_id) AS total_rental_count
FROM rental r
JOIN customer c
ON r.customer_id = c.customer_id
GROUP BY 1
ORDER BY 1, 2
),
/* table4 joins table2 and table3 to create a list of customer_id's of customers who have rented films with a female protagonist AND whose ratio of how many times they have rented such films to how many total films they have rented is greater than the ratio of films with a female protagonist to all films in the database. This is a list of customers with a preference for films with a female protagonist since they have rented films with a female protagonist at a higher rate than would result from a random renting of films. Note, I have deliberately chosen to use the ratio of film_id's rather than ratio of inventory_id's as a comparison to each customer's rental history, under the assumption that in general, having a greater number of copies of a certain film will not make it more likely to be rented. */
table4 AS (
SELECT t2.customer_id
FROM table2 t2
JOIN table3 t3
ON t2.customer_id = t3.customer_id
WHERE (CAST (t2.female_protagonist_rental_count AS FLOAT) / t3.total_rental_count) >
(CAST (
(
SELECT COUNT (*)
FROM table1
) AS FLOAT) /
(
SELECT COUNT (f.film_id)
FROM film f
)
)
),
/* table5 creates a table of every customer's customer_id, total amount of revenue they have generated through rental payments ("Customer Total Rental Revenue"), uses a Window Function to determine what Customer Total Rental Revenue quartile they are in, and whether they have rented films with a female protagonist at a higher rate than would result from a random renting of films ("Female Protagonist Positive") or not ("No Preference"). */
table5 AS (
SELECT c.customer_id,
SUM (p.amount) AS customer_total_rental_revenue,
NTILE (4) OVER (ORDER BY SUM (p.amount) DESC) AS customer_total_rental_revenue_quartile,
CASE WHEN c.customer_id IN (
SELECT *
FROM table4
) THEN 'female protagonist positive'
ELSE 'no preference' END AS female_protagonist_renter_profile
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY 1
),
/* table6 shows the quartiles of customers based on Customer Total Rental Revenue. Within each quartile, Customer Total Rental Revenue is summed for each of two categories of customers: (1) Female Protagonist Positive and (2) No Preference. Two additional columns are created to help format the final exported table for Excel */
table6 AS (
SELECT customer_total_rental_revenue_quartile,
female_protagonist_renter_profile,
SUM (customer_total_rental_revenue) AS customer_total_rental_revenue,
CONCAT ('$', LAG (SUM (customer_total_rental_revenue)) OVER (PARTITION BY customer_total_rental_revenue_quartile ORDER BY female_protagonist_renter_profile)) AS female_protagonist_positive,
CASE WHEN female_protagonist_renter_profile = 'no preference' THEN CONCAT ('$', SUM (customer_total_rental_revenue))
ELSE NULL END AS no_preference
FROM table5
GROUP BY 1, 2
ORDER BY 1, 2
)
/* the final output reformats table6 for Excel */
SELECT customer_total_rental_revenue_quartile,
female_protagonist_positive,
no_preference
FROM table6
WHERE no_preference IS NOT NULL;
Appendix
Key Finding
Total rental revenue from Female Protagonist Positive customers is nearly identical to No Preference customers in three of the four quartiles. Quartile 3 is the exception: Female Protagonist Positive customers represent under $5,000 in total rental revenue versus over $8,800 for No Preference customers.