CODEWITHSUNDEEP
sqlsqlite
Bobloblawlawblogs
Bobloblawlawblogs

Reputation: 323

Compare value in previous row SQL

Let's say you have some tables in sqllite like so

c.execute('''CREATE TABLE customers
         (customer_id INT, name VARCHAR)''')

c.execute('''CREATE TABLE orders
         (order_id INT, quantity INT, order_date DATETIME, customer_id INT)''')

How would you compare a customers order quantity between two different days?

I've gotten the query to a point where I have daily counts by customer

SELECT A.customer_id, name, SUM(quantity), strftime('%Y-%m-%d', order_date) d FROM orders " \
            "A LEFT JOIN customers B on A.customer_id = B.customer_id "\
            "GROUP BY d, A.customer_id " \
            "ORDER BY B.name LIMIT 20;

which yields me some results like this 

(0, 'customer_0', 423, '2018-03-27')
(0, 'customer_0', 1054, '2018-03-28')
(1, 'customer_1', 757, '2018-03-21')
(1, 'customer_1', 314, '2018-03-22')

I need to be able to compare a row with the previous row and get a diff like so - 

customer_0, 631
customer_1, -443

Upvotes: 1

Views: 155

Answers (1)

MatBailie
MatBailie

Reputation: 86716

SELECT
  C.customer_id,
  C.name,
  SUM(CASE WHEN strftime('%Y-%m-%d', O.order_date) = S.final_order_date THEN O.quantity END)
  -
  SUM(CASE WHEN strftime('%Y-%m-%d', O.order_date) = S.first_order_date THEN O.quantity END)
      AS delta_value
FROM
  customers    C
INNER JOIN
(
  SELECT
    customer_id,
    strftime('%Y-%m-%d', MIN(order_date))   first_order_date,
    strftime('%Y-%m-%d', MAX(order_date))   final_order_date
  FROM
    orders
  GROUP BY
    customer_id 
)
  S
    ON S.customer_id = C.customer_id
INNER JOIN
  orders    O
    ON O.customer_id = C.customer_id
GROUP BY
  C.customer_id,
  C.name
ORDER BY
  C.name
LIMIT
  20

Upvotes: 1

Related Questions