How can I count unique clicks across jobs for each category

I store link performance attributed to different categories in a sent table like this…

CREATE TABLE sent (
  customer_id INT,
  jobId INT,
  category VARCHAR(100),
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

I am trying to create a query that can show me the customer_id’s across the Y axis and the different categories across the X axix. What complicates this is that I only want count a click for every unique Job ID. I have attempted it using the query below but this is on just one category. The categories I want to search against are fixed (around 10 in total) and wanted to know how I can do this in the most efficient way. The table could have MILLIONS of records.

SELECT customer_id, count(customer_id) as unique_clicks_discovery
FROM (
  SELECT customer_id, jobId, COUNT(*) as discovery_clicks_unique
  FROM sent
  WHERE category = 'discovery'
  GROUP BY customer_id, jobId
) as discovery_click_roundup
GROUP BY customer_id

EDIT: Here are the expected results, please refer to the fiddle to see the data.

ACCOUNT ID    |   DISCOVER UNIQUE CLICKS  |  PURCHASE UNIQUE CLICKS
--------------------------------------------------------------------
101           |   3                       |  3
102           |   1                       |  1
103           |   1                       |  1
104           |   1                       |  2

EDIT 2: With sample data used:

INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 202, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'discovery');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 202, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 203, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (101, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (102, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (104, 202, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');
INSERT INTO sent (customer_id, jobId, category) VALUES (103, 201, 'purchase');

DB Fiddle (including sample data)

Answer

You can use conditional aggregation here:

SELECT customer_id
, COUNT(DISTINCT CASE category WHEN 'discovery' THEN jobId END) AS unique_clicks_discovery
, COUNT(DISTINCT CASE category WHEN 'purchase'  THEN jobId END) AS unique_clicks_purchase
FROM sent
GROUP BY customer_id;

You will have to append one COUNT(..) line per category.

Result:

| customer_id | unique_clicks_discovery | unique_clicks_purchase |
| ----------- | ----------------------- | ---------------------- |
| 101         | 3                       | 3                      |
| 102         | 1                       | 1                      |
| 103         | 1                       | 1                      |
| 104         | 1                       | 2                      |

View on DB Fiddle

Advertisement

Answer