Count how many times a certain value per user has changed

Michi Published at Dev

Michi

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    order_ID VARCHAR(255),
    customer_ID VARCHAR(255),
    score VARCHAR(255)
);

INSERT INTO sales
(order_date, order_ID, customer_ID, score)
VALUES 
('2020-04-10', 'Order_01', 'user_01', '1'),
('2020-04-15', 'Order_02', 'user_01', '5'),
('2020-05-18', 'Order_03', 'user_01', '4'),
('2020-05-22', 'Order_04', 'user_02', '1'),
('2020-06-18', 'Order_05', 'user_03', '3'),
('2020-06-26', 'Order_06', 'user_03', '2'),
('2020-07-28', 'Order_07', 'user_04', '2'),
('2020-08-03', 'Order_08', 'user_05', '1'),
('2020-09-10', 'Order_09', 'user_05', '1'),
('2020-09-15', 'Order_10', 'user_05', '1'),
('2020-10-23', 'Order_11', 'user_06', '4');

Expected Result:

order_date  | customer_ID  |  order_ID    |   score  |  score_changed  | orders_per_customer |
------------|--------------|--------------|----------|-----------------|---------------------|--
2020-04-10  |    user_01   |   order_01   |     1    |         2       |         1           |
2020-04-15  |    user_01   |   order_02   |     4    |         2       |         2           |
2020-05-18  |    user_01   |   order_03   |     5    |         2       |         3           |
------------|--------------|--------------|----------|-----------------|---------------------|--
2020-05-22  |    user_02   |   order_04   |     1    |         0       |         1           | 
------------|--------------|--------------|----------|-----------------|---------------------|--    
2020-06-18  |    user_03   |   order_05   |     3    |         1       |         1           | 
2020-06-26  |    user_03   |   order_06   |     2    |         1       |         2           | 
------------|--------------|--------------|----------|-----------------|---------------------|--
2020-07-28  |    user_04   |   order_07   |     2    |         0       |         1           | 
------------|--------------|--------------|----------|-----------------|---------------------|--
2020-08-03  |    user_05   |   order_08   |     1    |         0       |         1           | 
2020-09-10  |    user_05   |   order_09   |     1    |         0       |         2           | 
2020-09-15  |    user_05   |   order_10   |     1    |         0       |         3           | 
------------|--------------|--------------|----------|-----------------|---------------------|--
2020-10-23  |    user_06   |   order_11   |     4    |         0       |         1           |

In the results above I want to achieve two things:

(1) count the orders_per_customer
(2) count the changes of the score per customer

With the below query I was able to solve (1):

SELECT
s.order_date,
s.customer_ID AS customer_ID,
s.order_ID AS order_ID,
s.score AS score,
ROW_NUMBER() OVER (PARTITION BY s.customer_ID ORDER BY s.order_date) AS orders_per_customer
FROM sales s
GROUP BY 1,2,3,4
ORDER BY 1,2,3,5;

However, I have no clue how I need to change the query to also display the count of how many times the score per customer has changed as you can see in column score_changed.
Do you have any idea?

Serkan Arslan

After determines the changes, you can count them.

db_fiddle

SELECT order_date, customer_ID ,order_ID, score,
  SUM(score_changed) OVER (PARTITION BY customer_ID ) AS changes_of_the_score
  , orders_per_customer
FROM (
  SELECT
  s.order_date,
  s.customer_ID AS customer_ID,
  s.order_ID AS order_ID,
  s.score AS score,
  CASE WHEN score <> LAG(score) OVER (PARTITION BY s.customer_ID ORDER BY s.order_date) THEN 1 ELSE 0 END score_changed ,
  ROW_NUMBER() OVER (PARTITION BY s.customer_ID ORDER BY s.order_date) AS orders_per_customer
  FROM sales s
  GROUP BY order_date, customer_ID ,order_ID, score
) AS T
ORDER BY order_date, customer_ID ,order_ID, score;

Collected from the Internet

Please contact [email protected] to delete if infringement.