CRYPTOMARKETTRANSACTIONMONITORING.sql

    0

    0

    
    with difference as (
    
    SELECT
      *,
            DATEDIFF(minute,lag(dt) OVER (ORDER BY sender,dt),dt) as difF_minute,
        ROW_NUMBER() OVER(ORDER BY sender,dt) as rownumber
    FROM    krypto
    --ORDER BY sender,dt
      
    )
    ,rn as (
    SELECT
      rownumber
    FROM    difference
    WHERE   rownumber IN(
      SELECT
          rownumber
      FROM   krypto
      WHERE  abs(diff_minute) < 60
    )
    --order by sender,dt
    )
    
    ,sequences as (
    SELECT
      *
    FROM    difference
    WHERE rownumber IN(
    SELECT
      rownumber 
    FROM    rn
    UNION
    SELECT rownumber - 1 as rownumber
    FROM   rn
      )
    --order by sender,dt
    )
    SELECT
            sender,
            MIN(dt) as Sequence_start,
            MAX(dt) as Sequence_end,
            COUNT(rownumber) as transactions_count,
            SUM(amount) as transactions_sum
    FROM   sequences
    GROUP BY sender
    HAVING SUM(amount) >= 150
    order by sender,MIN(dt),MAX(dt)
    
    Codiga Logo
    Codiga Hub
    • Rulesets
    • Playground
    • Snippets
    • Cookbooks
    soc-2 icon

    We are SOC-2 Compliance Certified

    G2 high performer medal

    Codiga – All rights reserved 2022.