The SQL query presented here is a powerful tool for extracting critical information from our database. It focuses on retrieving data related to various financial transactions, including payments, loans, and more. By executing this query, users gain access to valuable insights such as transaction identifiers, types, associated loans, amounts, and statuses. This loan extract contains all your transactions and its child transactions.
customer_account_id: This field represents the identifier of the Canopy 'Account' entity that each transaction belongs to.
transaction_id: This field represents the unique identifier of the transaction, and it corresponds to the Line Item entity being tracked.
TRANSACTION_TYPE: Indicates the type of the transaction being tracked.
LOAN_ID: This field represents the identifier of the loan associated with the transaction. For transactions related to loans, it corresponds to the MCA (Merchant Cash Advance) transaction ID. For other transactions, it may be left blank or null. It is determined based on the paid_down_type, paid_down_parent_external_line_item_id, and paid_down_client_id columns in the aggregate_transactions CTE.
CREATED_AT: The date when the transaction became effective.
AMOUNT: The amount associated with the transaction. For transactions related to loans, it corresponds to the original draw amount. For PAYMENT_SPLIT transactions, it represents the amount allocated to the paid-down type.
TYPE: The type of the line item in the Canopy system.
STATUS: This field represents the status of the line item
/* This CTE is responsible for extracting data from the line_items table, specifically for transactions of type 'PAYMENT'. It's designed to retrieve crucial information from these payment transactions and prepare it for further processing. */ WITH payment_split_json_records AS ( select jsonb_array_elements(line_item_relationships) field, valid_at as effective_at, external_line_item_id as payment_external_line_item_id, account_id from data_direct.line_items where line_item_type = 'PAYMENT' ), /* This CTE transforms the data extracted in the previous CTE into a format suitable for analysis. It categorizes the transactions into 'PAYMENT_SPLIT', calculates the original amount, and determines attributes related to loans and other transaction details. */ splits_as_transactions AS ( select 'PAYMENT_SPLIT' as line_item_type, payment_external_line_item_id, (psjr.field ->> 'split_amount_cents') :: int as original_amount_cents, (psjr.field ->> 'line_item_client_id') as transaction_id, (psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id, (psjr.field ->> 'paid_down_line_item_type') as paid_down_type, ( psjr.field ->> 'paid_down_line_item_parent_client_id' ) as paid_down_parent_external_line_item_id -- we can use this to map to the loan , effective_at, account_id from payment_split_json_records psjr ), /* This CTE retrieves all transactions of types 'LOAN' and 'PAYMENT_REVERSAL' from the line_items table. It's primarily focused on loan-related transactions. */ loan_transactions AS ( select * from data_direct.line_items where line_item_type IN ('LOAN', 'PAYMENT_REVERSAL') ), /* The aggregate_transactions CTE combines the data from the previous CTEs, creating a unified view of various transaction attributes. */ aggregate_transactions AS ( select -- this is the Canopy 'Account' entity that every transaction belongs to account_id AS customer_account_id -- this is the original transaction line item id that can be used to retrieve it from the api , transaction_id -- the type of the transaction as tracked Line Item entity , line_item_type TRANSACTION_TYPE -- for items that can be linked back to the original MCA, this will be the MCA transaction id, other will be left blank, as it is not tied to the MCA but the Canopy Account entity that the MCA belongs to. , CASE paid_down_type WHEN 'MANUAL_FEE' THEN NULL ELSE COALESCE( paid_down_parent_external_line_item_id, paid_down_client_id ) END LOAN_ID -- when the transaction became effective , effective_at :: date CREATED_AT -- For the TRANSACTION_TYPE LOAN, the original draw amount -- For the TRANSACTION_TYPE PAYMENT_SPLIT, the amount allocated to the paid down type , original_amount_cents AMOUNT, paid_down_type AS TYPE -- The status of the line item in the Canopy system , 'SPLIT_VALID' STATUS FROM splits_as_transactions sat UNION SELECT account_id AS customer_account_id, external_line_item_id transaction_id, line_item_type TRANSACTION_TYPE, CASE line_item_type WHEN 'LOAN' THEN external_line_item_id END LOAN_ID, effective_at :: date as CREATED_AT, original_amount_cents AMOUNT, CASE line_item_type WHEN 'LOAN' THEN 'MCA' END AS TYPE, line_item_status STATUS FROM loan_transactions ) SELECT * FROM aggregate_transactions;
Updated 3 months ago