Mointoring querries

queries.sql

-- Duplicate snapshots
SELECT count(id) as "count", company_id FROM company_snapshots WHERE is_current = 1 GROUP BY company_id HAVING count>1;

-- Debtor financing limit out of sync
SELECT SUM(i.amount) as sumo, c.`debtor_current_invoice_financing_amount` as limi, c.id, i.updated_at, i.state, count(i.id)
FROM invoices i 
LEFT JOIN companies c ON c.id = i.`debtor_company_id`
WHERE i.state  IN ("paid_out", "payback", "fully_paid_back", "late", "financing_approved")  
GROUP BY i.`debtor_company_id`
HAVING sumo != limi
ORDER BY i.updated_at DESC;

-- Untagged transactions
SELECT count(cbsi.id) as "COUNT", CONCAT("{'user_id':'",uba.user_id,"'}")
FROM `customer_bank_statements` cbs 
LEFT JOIN `customer_bank_statement_items` cbsi ON cbsi.`customer_bank_statement_id` = cbs.id
LEFT JOIN `user_bank_accounts` uba ON uba.bank_account_id = cbs.`bank_account_id`
LEFT JOIN `bank_account_bank_data_providers` bcbdp ON bcbdp.`bank_account_id` = uba.`bank_account_id` AND bcbdp.is_active =1
WHERE cbsi.is_tagged = 0
AND uba.user_id IS NOT NULL
GROUP BY uba.user_id
ORDER BY "COUNT" DESC

-- Extract items for scoring
SELECT cbsi.id as "id", uba.id as "bank_account_id", uba.user_id as "user_id", cbsi.`category_id`, cbsi.`is_chargeback`, cbsi.`chargeback_category_label`, cbsi.`data_provider_amount`, cbsi.`data_provider_booking_date_time`, cbsi.`data_provider_creation_date_time`, cbsi.`data_provider_value_date`
FROM `customer_bank_statements` cbs 
LEFT JOIN `customer_bank_statement_items` cbsi ON cbsi.`customer_bank_statement_id` = cbs.id
LEFT JOIN `user_bank_accounts` uba ON uba.bank_account_id = cbs.`bank_account_id`
LEFT JOIN `bank_account_bank_data_providers` bcbdp ON bcbdp.`bank_account_id` = uba.`bank_account_id` AND bcbdp.is_active =1
WHERE  uba.user_id  =1288
LIMIT 10000;

-- Extracting balances for scroing
SELECT bab.id, uba.bank_account_id, uba.user_id, bab.balance, bab.`balance_date`, bab.`created_at`
FROM `bank_account_balances` bab
LEFT JOIN `user_bank_accounts` uba ON uba.bank_account_id = bab.`bank_account_id`
LEFT JOIN `bank_account_bank_data_providers` bcbdp ON bcbdp.`bank_account_id` = uba.`bank_account_id` AND bcbdp.is_active =1
WHERE  uba.user_id  =1288

-- Add toggle for all users
INSERT INTO `users_features` (`user_id`, `feature`, created_at, updated_at)
SELECT u.id, "non_recourse_factoring", NOW(), NOW()
FROM users u
WHERE u.roles LIKE "%CUSTOMER%"

-- Missing BA data

SELECT  uba.user_id,
        s.bank_account_id,
        cs.name AS company_name,
        i.outstanding,
        CASE WHEN uba.user_id IN (
SELECT DISTINCT owner_id FROM invoices 
WHERE state IN ('financing_requested', 'risk_checks_passed', 'financing_approved')
OR (state = 'data_finalized' AND state_changed_at > CURRENT_DATE - INTERVAL 10 DAY)
OR (state = 'paid_out' AND state_changed_at > CURRENT_DATE - INTERVAL 90 DAY)
)
             THEN 1
             ELSE 0
        END AS has_requested_invoice,
        max(i.data_provider_booking_date_time),
        max(i.created_at),
        bab.max_created_at
        
FROM customer_bank_statement_items i
INNER JOIN customer_bank_statements s
    ON i.customer_bank_statement_id = s.id
INNER JOIN user_bank_accounts uba
    ON uba.bank_account_id = s.bank_account_id
INNER JOIN bank_accounts b
    ON b.id = uba.bank_account_id   
INNER JOIN (SELECT bank_account_id, max(created_at) AS max_created_at FROM  bank_account_balances GROUP BY bank_account_id) bab
    ON b.id = bab.bank_account_id   
    
INNER JOIN (
SELECT owner_id, sum(outstanding_amount) AS outstanding FROM invoices GROUP BY OWNER_id
) i
ON i.owner_id = uba.user_id
LEFT JOIN users_companies uc
    ON uc.user_id = uba.user_id
    
LEFT JOIN company_snapshots cs
    ON cs.company_id = uc.company_id
    AND cs.is_current = 1
LEFT JOIN users u
    ON u.id = uba.user_id
    
WHERE b.core_mandate_valid_from IS NOT NULL
AND uba.bank_account_id NOT IN (157, 962, 290, 222)
AND u.state NOT IN ('canceled')
GROUP BY 
uba.user_id,
        s.bank_account_id,
        i.outstanding,
        cs.name




overwrite pep

UPDATE `risk_attributes` ra
SET ra.`initial_value` = ra.`attribute_value`, ra.`attribute_value` = 3, ra.`is_overwritten` = 1
WHERE ra.`attribute_name` IN ("count_compliance_summary") 
AND ra.is_current = 1
AND ra.`attribute_value` = 2
AND (ra.created_at BETWEEN "2019-06-26 00:00:00" AND "2019-06-26 23:59:59");


UPDATE `risk_attributes` ra
SET ra.`initial_value` = ra.`attribute_value`, ra.`attribute_value` = "b:1;", ra.`is_overwritten` = 1
WHERE ra.`attribute_name` IN ("ubos_pep_sanctions","company_pep_sanctions","singatories_pep_sanctions","all_ubos_found") 
AND ra.is_current = 1
AND ra.`attribute_value` = "b:0;"
AND (ra.created_at BETWEEN "2019-06-26 00:00:00" AND "2019-06-26 23:59:59");

UPDATE `risk_attributes` ra
SET ra.`initial_value` = ra.`attribute_value`, ra.`attribute_value` = "b:0;", ra.`is_overwritten` = 1
WHERE ra.`attribute_name` IN ("has_blacklist") 
AND ra.is_current = 1
AND ra.`attribute_value` = "b:1;"
AND (ra.created_at BETWEEN "2019-06-26 00:00:00" AND "2019-06-26 23:59:59");