SQL: Grouping certain columns while pivoting others

Data and Databases
1 
CREATE TABLE customer_data
(
  user_id  INT,
  session_id  STRING,
  source  STRING,
  purchase INT,
  metric INT,
  amount STRING
);

INSERT INTO customer_data VALUES
  (101, 'xyz789', 'bing', 2, 10, '1500'),
  (101, 'xyz789', 'bing', 2, 20, '30000'),
  (101, 'xyz789', 'bing', 2, 30, '2K to 4K'),
  (202, 'uvw456', 'twitter', 1, 10, '3200'),
  (202, 'uvw456', 'twitter', 1, 20, '65000'),
  (202, 'uvw456', 'twitter', 1, 30, '4K to 6K');

SELECT * FROM customer_data ORDER BY user_id, metric

I’m having trouble modifying this dataset. I need to group by user_id, session_id, source, and purchase while using metric and amount as pivoted columns. The final output should display separate columns for each metric value (10, 20, 30) with their associated amount. I’ve tried using pivot functions but I’m not sure how to set up the query. Any suggestions for how to accomplish this?

2

To achieve the desired result, you can use a combination of GROUP BY and conditional aggregation. Here’s a query that should work for your needs:

SELECT
    user_id,
    session_id,
    source,
    purchase,
    MAX(CASE WHEN metric = 10 THEN amount END) AS metric_10_amount,
    MAX(CASE WHEN metric = 20 THEN amount END) AS metric_20_amount,
    MAX(CASE WHEN metric = 30 THEN amount END) AS metric_30_amount
FROM customer_data
GROUP BY user_id, session_id, source, purchase
ORDER BY user_id;

This query groups the data as requested and creates separate columns for each metric value. The MAX function is used to handle potential multiple rows per group, though in your sample data this isn’t necessary. This approach is often more efficient than using PIVOT, especially when working with large datasets or complex groupings.

3

hey sophia39, u can try this version:

SELECT user_id, session_id, source, purchase,
MAX(CASE WHEN metric=10 THEN amount END) AS m10,
MAX(CASE WHEN metric=20 THEN amount END) AS m20,
MAX(CASE WHEN metric=30 THEN amount END) AS m30
FROM customer_data
GROUP BY user_id, session_id, source, purchase.

hopefully this works!

4

hey sophia! have you tried using a pivot table? i’m curious about your data - what kind of metrics are you tracking? maybe we could brainstorm some cool ways to visualize it? lemme know if u wanna chat more about sql tricks, i’m always eager to learn new stuff!