An Exhaustive Collection of Amazon SQL Interview Questions for Data Roles

-- Write a SQL query to get the average review ratings for every product every month.-- The output should include the month (in numerical value), product id, and average star rating rounded to two decimal places.-- Sort the output based on month followed by the product id.SELECT     EXTRACT(MONTH FROM submit_date) AS mth,    product_id,    ROUND(AVG(stars), 2) AS avg_starsFROM     reviewsGROUP BY     EXTRACT(MONTH FROM submit_date), product_idORDER BY     mth, product_id;

-- Identify the top two highest-grossing products within each category in 2022.-- Output the category, product, and total spend.WITH product_sales AS (    SELECT         category,         product,         SUM(spend) AS total_spend    FROM         transactions    WHERE         transaction_date BETWEEN '2022-01-01' AND '2022-12-31'    GROUP BY         category, product)SELECT     category,    product,     total_spendFROM (    SELECT         category,        product,        total_spend,        DENSE_RANK() OVER (PARTITION BY category ORDER BY total_spend DESC) AS rn    FROM         product_sales) rankedWHERE     rn <= 2ORDER BY     category, total_spend DESC;

-- Write a SQL query to create a histogram of the number of comments per user in January 2020.-- Assume bin buckets class intervals of one.SELECT     count_comments,    COUNT(*) AS num_usersFROM (    SELECT         user_id,        COUNT(*) AS count_comments    FROM         comments    WHERE         created_at BETWEEN '2020-01-01' AND '2020-01-31'    GROUP BY         user_id) subqueryGROUP BY     count_commentsORDER BY     count_comments;

-- Write a query to create a new table, flight_routes, that displays unique pairs of two locations.-- Duplicate pairs (e.g., Dallas to Seattle and Seattle to Dallas) should have one entry.CREATE TABLE flight_routes ASSELECT     DISTINCT LEAST(source_location, destination_location) AS source,    GREATEST(source_location, destination_location) AS destinationFROM     flights;

-- Write a query to get the cumulative number of new users added by day, with the total reset every month.SELECT     DATE_TRUNC('month', created_at)::DATE AS month_start,    created_at::DATE AS day,    COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', created_at) ORDER BY created_at) AS cumulative_usersFROM     usersORDER BY     month_start, day;

-- Write a query to output a table that includes every product name a user has ever purchased.SELECT DISTINCT    user_id,    product_nameFROM (    SELECT         u.user_id,        p.product_name,        DENSE_RANK() OVER (PARTITION BY u.user_id ORDER BY p.id) AS rn    FROM         users u    CROSS JOIN         products p    WHERE         p.id IN (SELECT product_id FROM purchases WHERE user_id = u.user_id)) subqueryORDER BY     user_id, rn;

-- Write a query to get the distribution of the number of conversations created by each user by day in 2020.SELECT     user_id,    created_at::DATE AS day,    COUNT(*) AS conversationsFROM (    SELECT DISTINCT        sender_id,        receiver_id,        created_at    FROM         messages    WHERE         created_at BETWEEN '2020-01-01' AND '2020-12-31') subqueryGROUP BY     user_id, dayORDER BY     user_id, day;

-- Write a SQL query to select the 2nd highest salary in the engineering department.-- If more than one person shares the highest salary, select the next highest salary.SELECT     salary AS second_highest_salaryFROM (    SELECT         salary,        DENSE_RANK() OVER (ORDER BY salary DESC) AS rn    FROM         employees e    JOIN         departments d ON e.department_id = d.id    WHERE         d.name = 'Engineering') subqueryWHERE     rn = 2LIMIT 1;

-- Write a query to identify the manager with the biggest team size.-- Assume there is only one manager with the largest team size.SELECT     m.name AS manager,    COUNT(*) AS team_sizeFROM     employees eJOIN     managers m ON e.manager_id = m.idGROUP BY     m.nameORDER BY     team_size DESCLIMIT 1;

-- Create a report displaying which shipments were delivered to customers during their membership period.-- If the transaction is shipped during the membership period, the is_member column should have 'Y'; otherwise, 'N'.SELECT     s.shipment_id,    s.customer_id,    s.shipment_date,    CASE        WHEN s.shipment_date BETWEEN m.start_date AND m.end_date THEN 'Y'        ELSE 'N'    END AS is_memberFROM     shipments sLEFT JOIN     memberships m ON s.customer_id = m.customer_idORDER BY     s.customer_id, s.shipment_date;

-- Write a query to analyze a time series dataset and identify potential discrepancies.WITH time_series AS (    SELECT         timestamp,        value,        LEAD(value, 1) OVER (ORDER BY timestamp) AS next_value,        LAG(value, 1) OVER (ORDER BY timestamp) AS prev_value    FROM         asset_prices)SELECT     timestamp,    value,    next_value,    prev_value,    CASE        WHEN next_value < prev_value THEN 'Potential Discrepancy'        ELSE 'OK'    END AS statusFROM     time_seriesORDER BY     timestamp;