Amazon Data Analyst SQL Interview Questions & Queries | Tutor Tips Inside

Amazon Data Analyst SQL Interview Questions: Real-World Queries & Pro Tips

Preparing for an Amazon Data Analyst SQL interview can feel like climbing a mountain. But with the right guidance, even the trickiest queries become manageable. Here’s a practical, scrollable guide with 13 essential SQL questions and pro tips from my experience as a tutor and freelance consultant.

SELECT customer_id, SUM(order_amount) AS total_purchase
FROM sales
GROUP BY customer_id
ORDER BY total_purchase DESC
LIMIT 5;
    

Pro Tip: Always check for NULLs in order_amount to avoid skewed results. Window functions can be used if you want top-N per region.

SELECT customer_id, order_date, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;
    

Pro Tip: Use ROW_NUMBER() OVER(PARTITION BY customer_id, order_date) if you need to remove duplicates and keep one record. Data ko duplicate mat hone do!

SELECT e.employee_id, e.name AS employee_name, d.name AS department_name, m.name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON e.manager_id = m.employee_id;
    

Pro Tip: LEFT JOIN ensures you don’t lose employees without managers. Always handle NULLs carefully!

SELECT product_category, SUM(order_amount) AS total_revenue
FROM orders
GROUP BY product_category;
    

Pro Tip: Aggregate by month or quarter for dashboards to improve performance. “Thoda plan ahead karo, kam tension hoga!”

SELECT order_id, order_date, delivery_date,
       DATEDIFF(day, order_date, delivery_date) AS days_to_deliver
FROM orders
WHERE delivery_date IS NOT NULL;
    

Pro Tip: Handle future or incorrect dates to avoid negative values. Data cleaning is half the battle.

SELECT product_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > 1000;
    

Pro Tip: Always double-check units. Sometimes sales data has mixed units—“Thoda dhyan se kaam karo!”

SELECT *
FROM customers
WHERE email IS NULL;
    

Pro Tip: Missing emails might indicate inactive customers. Handle these rows carefully in analysis.

SELECT id + 1 AS missing_id
FROM orders o1
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o2
  WHERE o2.id = o1.id + 1
);
    

Pro Tip: Gap detection is crucial in audit logs or sequential order IDs. “Chhoti chhoti cheezon ka dhyan rakho.”

SELECT MAX(curr_val - prev_val) AS max_diff
FROM (
  SELECT value AS curr_val, LAG(value) OVER (ORDER BY value) AS prev_val
  FROM table_name
) t;
    

Pro Tip: Use LAG/LEAD functions efficiently. Window functions are your best friend.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value
FROM table_name;
    

Pro Tip: Median is more robust than average for skewed data. “Average pe mat jao, median ka scene dekho.”

SELECT a.id AS range1, b.id AS range2
FROM date_table a
JOIN date_table b
  ON a.start_date <= b.end_date
 AND b.start_date <= a.end_date
WHERE a.id <> b.id;
    

Pro Tip: Useful in booking systems or scheduling. Don’t double-book your data!

SELECT *
FROM orders
PIVOT (
  SUM(order_amount)
  FOR product_category IN ('Electronics', 'Clothing', 'Books')
) AS p;
    

Pro Tip: Pivot for dashboards. “Visual banana hai to pivot karo, data ka mazaa aaega.”

SELECT *
FROM (
  SELECT *,
         LAG(column_name, n) OVER (ORDER BY id) AS prev_value
  FROM table_name
) t
WHERE column_name = prev_value;
    

Pro Tip: Spot patterns or repeated transactions. Window functions make life easier.