mirror of
https://github.com/FlipsideCrypto/ethereum-models.git
synced 2026-02-06 16:16:44 +00:00
50813e29f3
* freq table + modified bal diffs * research docs * add block number filter * delete test models add eth diff * no cache hints * merge optimizations * fix incremental to account for non-chron block loads * config updates, move to 25 hour look back * update to final * update sources * remove no cache * add no filter * add back <> * add late arriving balance test * add test to recent balances * remove comments * fix war * format * change test * add group by * remove test * v2 models * format and pause tests * re enable tests * format * format * format final * update to final * update test ref * format macro
79 lines
1.9 KiB
SQL
79 lines
1.9 KiB
SQL
{% test out_of_order_balance_diffs(model) %}
|
|
WITH ordered_balances AS (
|
|
SELECT
|
|
t.block_number,
|
|
t.block_timestamp,
|
|
t.address,
|
|
{% if model.identifier == 'token_balance_diffs_recent' %}
|
|
t.contract_address,
|
|
{% endif %}
|
|
|
|
t.prev_bal_unadj,
|
|
t.current_bal_unadj,
|
|
t._inserted_timestamp,
|
|
t.id,
|
|
COALESCE(LAG(t.current_bal_unadj) ignore nulls over(PARTITION BY t.address
|
|
{% if model.identifier == 'token_balance_diffs_recent' %}
|
|
, t.contract_address
|
|
{% endif %}
|
|
ORDER BY
|
|
t.block_number ASC), 0) AS actual_previous_balance,
|
|
CASE
|
|
WHEN LAG(
|
|
t.current_bal_unadj
|
|
) over(
|
|
PARTITION BY t.address
|
|
|
|
{% if model.identifier == 'token_balance_diffs_recent' %},
|
|
t.contract_address
|
|
{% endif %}
|
|
ORDER BY
|
|
t.block_number ASC
|
|
) IS NULL THEN TRUE
|
|
ELSE FALSE
|
|
END AS is_first_record
|
|
FROM
|
|
{{ model }}
|
|
t
|
|
)
|
|
SELECT
|
|
block_number,
|
|
block_timestamp,
|
|
address,
|
|
{% if model.identifier == 'token_balance_diffs_recent' %}
|
|
contract_address,
|
|
{% endif %}
|
|
|
|
prev_bal_unadj,
|
|
actual_previous_balance,
|
|
current_bal_unadj,
|
|
_inserted_timestamp,
|
|
id,
|
|
ABS(
|
|
prev_bal_unadj - actual_previous_balance
|
|
) AS difference,
|
|
is_first_record
|
|
FROM
|
|
ordered_balances
|
|
WHERE
|
|
prev_bal_unadj != actual_previous_balance
|
|
AND actual_previous_balance IS NOT NULL
|
|
AND is_first_record = FALSE
|
|
GROUP BY
|
|
block_number,
|
|
block_timestamp,
|
|
address,
|
|
{% if model.identifier == 'token_balance_diffs_recent' %}
|
|
contract_address,
|
|
{% endif %}
|
|
|
|
prev_bal_unadj,
|
|
actual_previous_balance,
|
|
current_bal_unadj,
|
|
_inserted_timestamp,
|
|
id,
|
|
difference,
|
|
is_first_record
|
|
HAVING
|
|
COUNT(*) > 0 {% endtest %}
|