SQL Solutions

SQL Puzzle Interview Playbook

Elevate your data modeling and query optimization skills through expert-curated solutions for complex SQL scenarios.

CRM Marketing Touch Streak Analysis

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: They had at least one marketing touch per week for at least three consecutive weeks (weeks are defined as starting on Monday). 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.

Server Fleet Utilization Analysis

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.

Alphabetical Continent Pivot

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.

Identify Retried Payment Transactions

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.

Monthly Department vs Company Salary Benchmarking

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'.

Finding Median Rows per Partition

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: Sort salaries in ascending order for each company. In case of identical salaries, use id as a secondary ascending sort key to break ties. If a company has an odd number of employees, return the single middle row. 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.

Page 1