Methodology:

Time Period for Analysis:
- Start Date: January 1st, 2023
- Cut-off Date: September 15th, 2023
Daily Prices Calculation:
- For each token, the average closing price is calculated on a daily basis from the flow.core.fact_hourly_prices table.
- This average daily price is used to convert amounts into USD for tokens other than USDC.
Loan Listing Indexer:
- For each loan listing event, various attributes such as listing date, expiry date, listing resource ID, amount, and payment token type are extracted.
- The payment token type is categorized into 'Flow', 'USDC', or 'Other' based on predefined strings.
Loan Funding Indexer:
- For each loan funding event, attributes like funding date, expected repayment amount, borrower, lender, and funding resource ID are determined.
- It also identifies if a loan is funded.
Loan Liquidated Indexer:
- For each loan liquidation event, attributes like liquidation date, liquidated amount, borrower, lender, and listing resource ID are extracted.
- It also identifies if a loan is liquidated.
Loan Repaid Indexer:
- For each loan repayment event, attributes like repayment date, repaid amount, borrower, lender, and listing resource ID are determined.
- It also identifies if a loan is repaid.
Mega Indexer:
- Combines data from the Loan Listing, Loan Funding, Loan Liquidated, and Loan Repaid indexers.
- Based on the combined data, the status of each loan is determined as 'Listed', 'Delisted', 'Active', 'Repaid', 'Liquidated', or 'Unknown'.

Tables Used:

flow.core.fact_hourly_prices:
- Contains hourly price data for different tokens.
- Used to calculate the average daily price for each token.
flow.core.fact_events:
- Contains event data related to transactions.
- Used to extract details for loan listings, fundings, liquidations, and repayments.

Important Definitions:

Daily Prices: The average closing price of tokens on a daily basis.
Loan Listing Indexer: A derived table that contains aggregated data for each loan listing event.
Loan Funding Indexer: A derived table that contains aggregated data for each loan funding event.
Loan Liquidated Indexer: A derived table that contains aggregated data for each loan liquidation event.
Loan Repaid Indexer: A derived table that contains aggregated data for each loan repayment event.
Mega Indexer: A comprehensive table that combines data from the Loan Listing, Loan Funding, Loan Liquidated, and Loan Repaid indexers and determines the status of each loan.
Payment Token Type: The type of currency used in the transaction. It can be 'Flow', 'USDC', or 'Other'.
Status: The current state of a loan, which can be 'Listed', 'Delisted', 'Active', 'Repaid', 'Liquidated', or 'Unknown'.

Flowty Loans 🤿