To 🔥 celebrate the holiday season, Also check out our NEWLY launched Annual plan (Up to 50% off). Use coupon code THANKSGIVING15 and enjoy additional 15% off all paid plans. Coupon expires at 2020/11/30 .
68. Spend difference between first and second rentals difficult

Instruction

  • Write a query to return the difference of the spend amount between the following customers' first movie rental and their second rental.
  • customer_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10).
  • Use first spend - second spend to compute the difference.
  • Skip users who only rented once.
Hint:
  • You can use ROW_NUMBER to identify the first and second transactions.
  • You can use LAG or LEAD to find previous or following transaction amount.

Table: payment

Movie rental payment transactions table

   col_name   | col_type
--------------+--------------------------
 payment_id   | integer
 customer_id  | smallint
 staff_id     | smallint
 rental_id    | integer
 amount       | numeric
 payment_ts   | timestamp with time zone

  

Sample results

 customer_id | delta
-------------+-------
           1 |  2.00
           2 |  2.00
           3 | -1.00
           4 |  4.00
           5 |  0.00

Subscribe for solution