Signals

                SELECT
                    DATE_TRUNC('day', o.funded_at) AS metric_date,
                    COUNT(CASE WHEN o.fraud_flag = 1 AND o.actual_fraud = 0 THEN 1 END) AS false_positives,
                    COUNT(CASE WHEN o.fraud_flag = 1 THEN 1 END) AS total_flagged,
                    CASE 
                        WHEN COUNT(CASE WHEN o.fraud_flag = 1 THEN 1 END) > 0 
                        THEN (CAST(COUNT(CASE WHEN o.fraud_flag = 1 AND o.actual_fraud = 0 THEN 1 END) AS DECIMAL) / 
                              COUNT(CASE WHEN o.fraud_flag = 1 THEN 1 END)) * 100
                        ELSE 0 
                    END AS false_positive_rate
                FROM fraud_flagged_txn_prod_25
                ORDER BY metric_date DESC;
                

                SELECT
                    DATE_TRUNC('month', o.funded_at) AS cohort_month,
                    COUNT(DISTINCT CASE WHEN b.chargeoff_amount IS NULL AND b.days_past_due >= 60 THEN o.loan_id END) * 1.0
                        / COUNT(DISTINCT o.loan_id) AS dq_60_rate
                FROM originations o
                LEFT JOIN backbook_valuations b ON o.loan_id = b.loan_id
                WHERE o.funded_at IS NOT NULL
                GROUP BY 1
                ORDER BY 1;
                

                SELECT
                    DATE_TRUNC('day', m.prediction_date) AS metric_date,
                    AVG(ABS(m.actual_value - m.predicted_value)) AS mean_absolute_residual,
                    AVG(m.actual_value - m.predicted_value) AS mean_residual,
                    STDDEV(m.actual_value - m.predicted_value) AS residual_std_dev,
                    COUNT(*) AS total_predictions,
                    CASE 
                        WHEN AVG(ABS(m.actual_value - m.predicted_value)) > 0
                        THEN (AVG(ABS(m.actual_value - m.predicted_value)) / AVG(m.actual_value)) * 100
                        ELSE 0 
                    END AS mean_absolute_percentage_error
                FROM fraud_exploratory_signals_prod_us m
                WHERE m.prediction_date >= CURRENT_DATE - INTERVAL '90 days'
                    AND m.actual_value IS NOT NULL
                GROUP BY metric_date, m.model_name
                ORDER BY metric_date DESC, m.model_name;
            

                SELECT
                    DATE_TRUNC('day', a.application_timestamp) AS metric_date,
                    COUNT(CASE WHEN a.final_decision = 'APPROVED' THEN 1 END) AS approved_applications,
                    COUNT(a.application_id) AS total_applications,
                    (CAST(COUNT(CASE WHEN a.final_decision = 'APPROVED' THEN 1 END) AS DECIMAL) / COUNT(a.application_id)) * 100 AS approval_rate
                FROM all_apps_raw_replicate_prod_us a
                WHERE a.employment_type IN ('self-employed', '1099') 
                    AND a.doc_status = 'incomplete'
                GROUP BY metric_date
                ORDER BY metric_date DESC;
                

                SELECT
                    DATE_TRUNC('month', o.funded_at) AS metric_date,
                    COUNT(CASE WHEN b.chargeoff_amount > 0 OR b.days_past_due >= 90 THEN 1 END) AS defaulted_loans,
                    COUNT(o.loan_id) AS total_loans,
                    (CAST(COUNT(CASE WHEN b.chargeoff_amount > 0 OR b.days_past_due >= 90 THEN 1 END) AS DECIMAL) / COUNT(o.loan_id)) * 100 AS default_rate
                FROM originations o
                LEFT JOIN backbook_valuations b ON o.loan_id = b.loan_id
                WHERE o.fico_score BETWEEN 580 AND 620
                    AND o.loan_type = 'auto'
                    AND o.state_addr IN ('FL', 'CA')
                GROUP BY metric_date
                ORDER BY metric_date DESC;
                

                SELECT
                    DATE_TRUNC('day', f.pageview_ts) AS funnel_day,
                    COUNT(DISTINCT a.application_id) * 1.0
                        / COUNT(DISTINCT f.session_id) AS takeup_rate
                FROM checkout_funnel f
                LEFT JOIN all_apps_raw_replicate_prod_us a
                ON f.session_id = a.session_id
                AND a.submitted_at BETWEEN f.pageview_ts AND f.pageview_ts + INTERVAL '2 hour'
                WHERE f.step_name = 'offer_shown'
                GROUP BY 1
                ORDER BY 1;
                

                SELECT
                    DATE_TRUNC('month', o.funded_at) AS origination_month,
                    COUNT(DISTINCT CASE WHEN b.chargeoff_amount > 0 THEN o.loan_id END) * 1.0
                        / COUNT(DISTINCT o.loan_id) AS chargeoff_rate
                FROM originations o
                LEFT JOIN backbook_valuations b ON o.loan_id = b.loan_id
                WHERE o.funded_at IS NOT NULL
                GROUP BY 1
                ORDER BY 1;
            

                    SELECT
                        cl.merchant_id,
                        cl.product_type_at_origination,
                        cl.initial_credit_tier,
                        cl.is_first_time_borrower,
                        cl.ltv_value
                        MAX(cl.calculation_date) AS latest_calculation_date
                    FROM
                        customer_ltv AS cl
                

                SELECT
                    DATE_TRUNC('day', a.application_timestamp) AS application_date,
                    COUNT(CASE WHEN a.final_decision = 'APPROVED' THEN a.application_id END) AS approved_low_aov_apps,
                    COUNT(a.application_id) AS total_low_aov_apps,
                    (CAST(COUNT(CASE WHEN a.final_decision = 'APPROVED' THEN a.application_id END) AS DECIMAL) / COUNT(a.application_id)) * 100 AS weekly_low_aov_approval_rate
                FROM
                    all_apps_raw_replicate_prod_us AS a
                WHERE
                    AND a.requested_loan_amount < 500.00 -- Defining 'Low AOV' as < $500
                GROUP BY
                    application_date
                ORDER BY
                    application_date DESC;
                

                SELECT
                    DATE_TRUNC('day', lpe.event_timestamp) AS payment_date,
                    SUM(CASE WHEN lpe.event_status = 'Failed' THEN 1 ELSE 0 END) AS failed_attempts,
                    COUNT(lpe.event_id) AS total_attempts,
                    (CAST(SUM(CASE WHEN lpe.event_status = 'Failed' THEN 1 ELSE 0 END) AS DECIMAL) / COUNT(lpe.event_id)) * 100 AS weekly_failure_rate
                FROM
                    loan_payment_events AS lpe
                WHERE
                    AND lpe.event_type = 'Payment Attempt'
                    AND lpe.payment_method_type IN ('ACH', 'Debit Card')
                GROUP BY
                    payment_date
                ORDER BY
                    payment_date DESC;
                

                SELECT
                    DATE_TRUNC('day', o.funded_at) AS metric_date, -- Date when the second loan was funded/observed
                    COUNT(DISTINCT customer_id_with_second_loan) AS second_time_users,
                    COUNT(DISTINCT customer_id_with_first_loan) AS total_first_time_users_in_period,
                    (CAST(COUNT(DISTINCT customer_id_with_second_loan) AS DECIMAL) / COUNT(DISTINCT customer_id_with_first_loan)) * 100 AS second_time_user_rate
                FROM (
                    SELECT
                        o1.customer_id AS customer_id_with_first_loan,
                        o2.customer_id AS customer_id_with_second_loan,
                        o2.funded_at -- To group by the date of the second loan
                    FROM
                        originations AS o1
                    JOIN
                        originations AS o2 ON o1.customer_id = o2.customer_id
                    WHERE
                        o1.funded_at >= CURRENT_DATE - INTERVAL '90 days' -- Consider users whose *first* loan was in the last 90 days
                        AND o2.funded_at >= o1.funded_at -- Ensure the second loan is after or on the same day as the first
                        AND o1.loan_id != o2.loan_id -- Ensure it's a different loan
                    GROUP BY
                        o1.customer_id, o2.customer_id, o2.funded_at
                    HAVING
                        COUNT(DISTINCT o1.loan_id) >= 1 -- Ensure they had at least one loan in the period
                        AND COUNT(DISTINCT o2.loan_id) >= 2 -- Ensure they had at least two loans in total
                ) AS subquery
                GROUP BY
                    metric_date
                ORDER BY
                    metric_date DESC;