SQL Puzzle Interview Playbook

Given a `product_spend` table documenting customer transactions, write a query to retrieve the top two highest-grossing products within each category for the calendar year 2022. Gross spend is defined as the sum of the `spend` column for a given product. Your output should include the category name, the product name, and the total calculated spend, ordered by category and then by the highest spend.

You are given a table `user_content` with a column `content_text`. Your task is to generate a report that transforms the text into Title Case based on specific business rules. 1. The first letter of every word must be capitalized, and all subsequent letters in that word must be lowercase. 2. The transformation must recognize a hyphen (-) as a word boundary, meaning segments on both sides of a hyphen (e.g., 'high-speed') must be capitalized ('High-Speed'). 3. All original spacing, including multiple consecutive spaces, must be preserved exactly as they appear in the source data. Return the original `content_id`, `content_text`, and the newly `transformed_text` column.

You are given a table `Stadium` containing stadium visit data with columns `id` (integer), `visit_date` (date), and `people` (integer). The `id` is an auto-incrementing value that corresponds with the chronological order of the `visit_date`. Write a SQL query to identify all sequences of three or more consecutive IDs where each visit in that sequence had a population of 100 or more people. The result should include the original row details and be ordered by the visit date.

Given a `Student` table containing names and IDs, and an `Exam` table containing scores for various exams, define a 'Quiet Student' as one who has participated in at least one exam but has never achieved the highest or lowest score in any exam they sat for. Write a SQL query to find the `student_id` and `student_name` of all quiet students, ensuring that students who never took an exam are excluded. Note: If an exam has multiple students with the same top score, all are considered 'loud' for that exam. Sort the output by `student_id`.

Given a table `measurements` containing `measurement_id`, `measurement_value`, and `measurement_time`, calculate the daily sum of values based on their chronological order within each day. A measurement is 'odd-numbered' if it is the 1st, 3rd, or 5th recorded event of that day, and 'even-numbered' if it is the 2nd, 4th, or 6th. Your output should contain the date, the total sum for odd-numbered measurements, and the total sum for even-numbered measurements for each day represented in the dataset.

A marketing team wants to reward highly engaged potential customers. Given a `marketing_touches` table (event_id, contact_id, event_type, event_date) and a `crm_contacts` table (contact_id, email), find the email addresses of all contacts who meet two conditions: 1. They had at least one marketing touch per week for at least three consecutive weeks (weeks are defined as starting on Monday). 2. They have performed at least one touch of type 'trial_request' at any point in their history. Ensure the solution handles contacts with multiple touches in a single week correctly and accounts for streaks that might cross year boundaries.

A cloud provider tracks server uptime through a series of status logs. You are given a table `server_utilization` with columns `server_id` (int), `status_time` (timestamp), and `session_status` (string, either 'start' or 'stop'). Each server can start and stop multiple times throughout the period. Calculate the total uptime for the entire fleet of servers. The result should be returned as the total number of full days (24-hour periods) of cumulative uptime across all servers, rounded down to the nearest integer. Ensure your solution accounts for the chronological order of events per server and correctly pairs start events with their subsequent stop events.

Given a `transactions` table with columns `transaction_id`, `merchant_id`, `credit_card_id`, `amount`, and `transaction_timestamp`, write a query to identify the total number of 'accidental' repeat payments. A repeat payment is defined as a transaction that occurs at the same merchant, with the same credit card, for the exact same amount, within a 10-minute window of a previous transaction. Note: In a sequence of three identical transactions each within 10 minutes of the last, the second and third should be counted as repeats, but the first should not.

Given a table `Student` containing `name` and `continent` columns (with potential duplicates), pivot the table so that student names are grouped under their respective continent headers: 'America', 'Asia', and 'Europe'. Within each column, names must be sorted alphabetically. If one continent has more students than another, the extra rows for the smaller continents should be filled with `NULL`. The result should show the first alphabetical student for each continent in the first row, the second in the second row, and so on.

You are given two tables: 'Salary' (containing 'id', 'employee_id', 'amount', and 'pay_date') and 'Employee' (containing 'employee_id' and 'department_id'). For every month present in the data, determine if the average salary within each department was higher, lower, or equal to the average salary of the entire company for that same month. Return a result table with the month (formatted as 'YYYY-MM'), the 'department_id', and a 'comparison' column indicating 'higher', 'lower', or 'same'.

Given a table `Employee` with columns `id` (INT, PK), `company` (VARCHAR), and `salary` (INT), write a SQL query to identify the specific rows that represent the median salary for each company. Rules for median calculation: 1. Sort salaries in ascending order for each company. 2. In case of identical salaries, use `id` as a secondary ascending sort key to break ties. 3. If a company has an odd number of employees, return the single middle row. 4. If a company has an even number of employees, return the two middle rows. The result should include the `id`, `company`, and `salary` columns for these specific median records.

Given a table `filed_taxes` with columns `filing_id`, `user_id`, `filing_date`, and `product`, find all users who have filed their taxes using any 'TurboTax' product for at least three consecutive years. Note that 'TurboTax' products may have different version names (e.g., 'TurboTax Basic', 'TurboTax Deluxe'). Assume each user files at most once per calendar year. Return the results sorted by `user_id`.