history.sql

-- HISTORICAL
set ts_from = TIME_SLICE(1743754858::timestamp_ntz, 5, 'MINUTE', 'START');
set ts_to = TIME_SLICE(1743927658::timestamp_ntz, 5, 'MINUTE', 'START');
SET asset_id = (SELECT METRICS_DEV.PUBLIC.GET_ASSET_ID_BY_REF(11559779572935330088));
SET metric_id = (SELECT METRICS_DEV.PUBLIC.GET_METRIC_ID_BY_NAME('transaction_volume'));

CREATE OR REPLACE TEMPORARY TABLE metrics_dev.public.INTERVALS AS (WITH RECURSIVE time_slices AS (    
    SELECT 
        $ts_from AS slice_start,
        TIME_SLICE($ts_from, 5, 'MINUTE', 'END') AS slice_end    
    UNION ALL
    
    SELECT 
        slice_end AS slice_start,
        DATEADD('MINUTE', 5, slice_end) AS slice_end
    FROM time_slices
    WHERE slice_start < $ts_to
)
SELECT ts.slice_start, ts.slice_end 
FROM time_slices ts);

SELECT * from metrics_dev.public.INTERVALS;


DECLARE
  intervals CURSOR FOR SELECT slice_start, slice_end FROM metrics_dev.public.INTERVALS;
  ts_start timestamp_ntz:=0;
  ts_stop timestamp_ntz:=0;
  batch NUMBER:=0;
BEGIN
  CREATE OR REPLACE TEMPORARY TABLE metrics_dev.public.FILTERED_SRC AS (
    SELECT block_timestamp, amount, block_height FROM solana.assets.transfers WHERE
            block_timestamp >= $ts_from
        AND block_timestamp < $ts_to
        AND program_name = 'system' and symbol = 'SOL'
        AND from_address != to_address
        ORDER BY block_timestamp
  );
  CREATE OR REPLACE TEMPORARY TABLE metrics_dev.public.JOB like metrics_dev.public.intraday_metrics;
  FOR interval IN intervals DO
      ts_start := interval.slice_start;
      ts_stop := interval.slice_end;
      batch := batch + 1;
      ASYNC (INSERT INTO metrics_dev.public.JOB(asset_id, metric_id, dt, value, is_finalized, seq_num, computed_at, block_number) (
        SELECT
            $asset_id AS asset_id,
            $metric_id AS metric_id,
            TIME_SLICE(block_timestamp, 5, 'MINUTE', 'START') AS dt,
            sum(amount) AS value,
            true as is_finalized,
            0 as SEQ_NUM,
            CURRENT_TIMESTAMP()::TIMESTAMP_NTZ AS COMPUTED_AT,
            max(block_height) AS BLOCK_NUMBER
                FROM metrics_dev.public.FILTERED_SRC
                WHERE
                        dt >= :ts_start
                    AND dt < :ts_stop
            GROUP BY dt
      ));
      IF (batch = 40) THEN
        AWAIT ALL;        
        batch := 0;
      END IF;
  END FOR;
  AWAIT ALL;  
END;

SELECT * from metrics_dev.public.JOB;