Quietly Rented Board Games

Problem

A board game cafe lends out games and tracks every rental.

Table: game
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| game_id | int |
| title | varchar |
| shelved_on | date |
+--------------+---------+
game_id is the primary key. shelved_on is the date the game first became available to rent.

Table: rental
+--------------+------+
| Column Name | Type |
+--------------+------+
| rental_id | int |
| game_id | int |
| copies | int |
| rented_on | date |
+--------------+------+
rental_id is the primary key. Each row records that some copies of a game were rented on a date.

Write a solution to report the games that were rented fewer than 10 copies in the last year, excluding any game that has been on the shelf for less than one month. Assume today is 2023-06-23, so 'last year' means rentals on or after 2022-06-23, and a game counts as too new if it was shelved on or after 2023-05-23. Return columns game_id and title in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns game_id, title.

Starter code (SQL)

SELECT *
FROM game;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium