Write a query to find all patients who missed at least two scheduled appointments in the past year.
-
SQL
- Write a SQL query to find the second highest salary from an Employees table without using LIMIT or TOP.
- Write a query to find all customers who placed orders in every month of 2025.
- Write a query to identify duplicate rows in a Transactions table based on customer_id, amount, and date.
- Write a query using GROUP BY and HAVING to find departments with more than 10 employees earning above the department average salary.
- Write a query to return the top 3 selling products per category using a subquery (no window functions allowed).
- Write a query to calculate the running total of daily revenue for a store using a self-join.
- Write a query to find the average order value per customer segment, excluding cancelled orders.
- Write a query to find employees who earn more than their direct manager, using a self-join on an Employees table.
- Write a query to list all customers who have not placed any order in the last 90 days.
- Write a query to compute the percentage of total revenue contributed by each product category.
- Write a query to find the first purchase date and last purchase date for every customer.
- Write a query to find the three most recent orders for each customer.
- Write a query that returns customers whose total spend is above the 90th percentile of all customers.
- Write a query to find customers who bought Product A but never bought Product B.
- Write a query to count how many orders each customer placed and rank customers by order count within their city.
- Write a query to identify the busiest hour of the day for support ticket creation using a Tickets table.
- Write a query to find the top 5 referral sources by conversion rate from a Signups table.
- Write a query using EXISTS to find suppliers who have never had a late delivery.
- Write a query to detect gaps in a sequential invoice_number column.
- Write a query to find all products whose price increased in the last price update compared to the previous one.
- Write a query to calculate month-over-month growth rate in active users from a DailyActiveUsers table.
- Write a query to return the department with the highest average tenure of employees.
- Write a query to find all orders placed on weekends versus weekdays and compare average order value.
- Write a query to find customers who upgraded their subscription plan more than once in the last year.
- Write a query to return the top 10% of sales reps by revenue generated in Q1.
- Write a query using a CTE to calculate a 7-day moving average of website visits.
- Write a query to find all patients who missed at least two scheduled appointments in the past year.
- Write a query to identify employees who have never taken a single day of leave in the last two years.
- Write a query to compute year-over-year revenue comparison for each region using self-joins.
- Write a query to find the longest streak of consecutive days a user logged into an app.
- Write a query to identify vendors whose invoice amounts differ by more than 15% from their historical average.
- Write a query to find all students who scored above the class average in more than 3 subjects.
- Write a query to detect accounts with more than 5 failed login attempts within a 10-minute window.
- Write a query to find the second most frequently ordered item for each customer.
- Write a query to identify products frequently bought together, based on an OrderItems table.
- Write a query to find the average time between a support ticket being opened and first responded to, grouped by priority.
- Write a query to find the top-selling product in each store location for the current quarter.
- Write a query to list all foreign keys referencing a given table in a schema (engine-specific system tables allowed).
- Write a query to compute the cumulative distinct count of new customers acquired each month.
Answer
Count missed ("no-show") appointments per patient within the past year, then keep patients with at least two.
Solution
SELECT patient_id, COUNT(*) AS missed_count
FROM Appointments
WHERE status = 'no-show'
AND appointment_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY patient_id
HAVING COUNT(*) >= 2;Step by Step
WHERE status = 'no-show'filters to only missed appointments, and the date condition restricts to the trailing 12 months.GROUP BY patient_idcollapses each patient's missed appointments into one row.HAVING COUNT(*) >= 2keeps only patients who missed two or more appointments in that window.
Alternative: Distinguishing "Missed" from "Cancelled"
SELECT patient_id, COUNT(*) AS missed_count
FROM Appointments
WHERE status IN ('no-show', 'missed')
AND appointment_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY patient_id
HAVING COUNT(*) >= 2;Real scheduling systems often distinguish "no-show" (patient didn't cancel and didn't attend) from "cancelled" (patient proactively cancelled, sometimes with notice) - these represent very different behaviors, and only genuine no-shows/missed visits should typically count toward this kind of flag, not late cancellations.
Key Exam Traps
- "Missed" vs "cancelled" is the key business distinction here - conflating the two status values would unfairly flag patients who gave proper notice alongside those who simply didn't show up, a meaningfully different clinical/operational signal.
- "At least two" means >= 2, not > 2 - a common off-by-one mistake when translating "at least" into a comparison operator.
- Same-day duplicate appointment records: If a rescheduled appointment creates two rows for what was really one missed visit, this query could double-count - worth checking whether the appointment table has a way to distinguish original bookings from reschedules.