6 Replies Latest reply on Oct 4, 2018 10:53 PM by James D

    Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

    James D

      Hello. Hopefully someone knows an elegant solution to this problem.

       

      My data is as follows (millions of rows):

       

      customer_iddatetransaction_idtransaction_type
      A1/1/1812345sale
      B2/20/1865899sale
      A1/29/1812345refund
      B3/29/1865899refund
      C4/1/18356987sale
      A1/20/1865987sale
      C6/1/18356988sale
      D9/1/183216549sale

       

      I want a table calc that shows, for each customer, the % of their transactions that were transaction_type = sale and refunded (based on matching transaction_id) within 30 days of the sale date. So it would be like this:

       

      customer_idsales 30 days old, or older (relative to today's actual date of Sept 26 2018)refunds occurring within 30 days of saletable_calc
      A2150%
      B100%
      C200%
      D000%

       

      A has 2 sales from at least 30 days ago, and has 1 refund that occurred within 30 days of the sale.

      B has 1 sale from at least 30 days ago, and has 0 refunds that occurred within 30 days of the sale, and has 1 refund that occurred more than 30 days after the sale, so is not counted by the table_calc.

      C has 2 sales from at least 30 days ago. and 0 refunds.

      D has 1 sale that occurred less than 30 days ago, so this is not counted by the table_calc.

       

      Thanks