Timescale crypto dashboard

caggs.SQL

--DROP MATERIALIZED VIEW tick1mcagg, tick10mcagg, tick30mcagg, tick1hcagg, tick6hcagg, tick12hcagg, tick1dcagg, tick7dcagg, tick14dcagg, tick30dcagg;
--DROP MATERIALIZED VIEW fl1mcagg, fl10mcagg, fl30mcagg, fl1hcagg, fl6hcagg, fl12hcagg, fl1dcagg, fl7dcagg, fl14dcagg, fl30dcagg;

--tickcaggs
CREATE MATERIALIZED VIEW tick1mcagg WITH (timescaledb.continuous) AS                                                                                                           
SELECT time_bucket('1m', time) as time, avg(price) as price, symbol
FROM crypto_ticks                                                                                                                          
GROUP BY time_bucket('1m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick10mcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('10m', time) as time, avg(price) as price, symbol
FROM crypto_ticks
GROUP BY time_bucket('10m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick30mcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('30m', time) as time, avg(price) as price, symbol 
FROM crypto_ticks
GROUP BY time_bucket('30m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick1hcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('1h', time) as time, avg(price) as price, symbol
FROM crypto_ticks 
GROUP BY time_bucket('1h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick6hcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('6h', time) as time, avg(price) as price, symbol
FROM crypto_ticks 
GROUP BY time_bucket('6h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick12hcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('12h', time) as time, avg(price) as price, symbol
FROM crypto_ticks 
GROUP BY time_bucket('12h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick1dcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('1d', time) as time, avg(price) as price, symbol
FROM crypto_ticks  
GROUP BY time_bucket('1d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick7dcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('7d', time) as time, avg(price) as price, symbol
FROM crypto_ticks
GROUP BY time_bucket('7d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick14dcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('14d', time) as time, avg(price) as price, symbol
FROM crypto_ticks
GROUP BY time_bucket('14d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW tick30dcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('30d', time) as time, avg(price) as price, symbol
FROM crypto_ticks
GROUP BY time_bucket('30d', time), symbol WITH NO DATA;

--flcaggs

CREATE MATERIALIZED VIEW fl1mcagg WITH (timescaledb.continuous) AS                                                                                                           
SELECT time_bucket('1m', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks                                                                                                                          
GROUP BY time_bucket('1m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl10mcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('10m', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks
GROUP BY time_bucket('10m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl30mcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('30m', time) as time, first(price, time) as f, last(price, time) as l, symbol 
FROM crypto_ticks
GROUP BY time_bucket('30m', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl1hcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('1h', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks 
GROUP BY time_bucket('1h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl6hcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('6h', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks 
GROUP BY time_bucket('6h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl12hcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('12h', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks 
GROUP BY time_bucket('12h', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl1dcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('1d', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks  
GROUP BY time_bucket('1d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl7dcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('7d', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks
GROUP BY time_bucket('7d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl14dcagg WITH (timescaledb.continuous) AS 
SELECT time_bucket('14d', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks
GROUP BY time_bucket('14d', time), symbol WITH NO DATA;

CREATE MATERIALIZED VIEW fl30dcagg WITH (timescaledb.continuous) AS
SELECT time_bucket('30d', time) as time, first(price, time) as f, last(price, time) as l, symbol
FROM crypto_ticks
GROUP BY time_bucket('30d', time), symbol WITH NO DATA;

GRANT SELECT ON tick1mcagg, tick10mcagg, tick30mcagg, tick1hcagg, tick6hcagg, tick12hcagg, tick1dcagg, tick7dcagg, tick14dcagg, tick30dcagg TO readaccess;
GRANT SELECT ON fl1mcagg, fl10mcagg, fl30mcagg, fl1hcagg, fl6hcagg, fl12hcagg, fl1dcagg, fl7dcagg, fl14dcagg, fl30dcagg TO readaccess;

CALL refresh_continuous_aggregate('tick1mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick10mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick30mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick1hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick6hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick12hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick1dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick7dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick14dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('tick30dcagg', NULL, NULL);

CALL refresh_continuous_aggregate('fl1mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl10mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl30mcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl1hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl6hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl12hcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl1dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl7dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl14dcagg', NULL, NULL);
CALL refresh_continuous_aggregate('fl30dcagg', NULL, NULL);

SELECT add_continuous_aggregate_policy('tick1mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick10mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick30mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick1hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick6hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick12hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick1dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick7dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick14dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('tick30dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl1mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl10mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl30mcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl1hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl6hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl12hcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl1dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl7dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl14dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');
SELECT add_continuous_aggregate_policy('fl30dcagg',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');

 
SELECT add_compression_policy('tick1mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick10mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick30mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick1hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick6hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick12hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick1dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick7dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick14dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('tick30dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl1mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl10mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl30mcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl1hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl6hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl12hcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl1dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl7dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl14dcagg', compress_after=>'45 days'::interval);
SELECT add_compression_policy('fl30dcagg', compress_after=>'45 days'::interval);

ALTER TABLE crypto_ticks SET (timescaledb.compress);
SELECT add_compression_policy('stocks_real_time', INTERVAL '2 weeks');
SELECT add_tiering_policy('crypto_ticks', INTERVAL '4 weeks');

CREATE MATERIALIZED VIEW one_day 
WITH (timescaledb.continuous) AS (
	SELECT time_bucket('1 day', time) AS time,
		AVG(*) AS tx_count,
		SUM(fee_usd) AS total_fee_usd,
		avg(weight) AS avg_tx_weight
	FROM transactions
	GROUP BY time_bucket('1 day', time)
);



CREATE MATERIALIZED VIEW block_one_day WITH (timescaledb.continuous) AS (
 SELECT time_bucket('1 day', "time") AS bucket,
    block_id,
    count(*) AS tx_count,
    sum(fee) AS block_fee_sat,
    sum(fee_usd) AS block_fee_usd,
    stats_agg(fee) AS stats_tx_fee_sat,
    avg(size) AS avg_tx_size,
    avg(weight) AS avg_tx_weight,
    sum(size) AS block_size,
    sum(weight) AS block_weight,
    max(size) AS max_tx_size,
    max(weight) AS max_tx_weight,
    min(size) AS min_tx_size,
    min(weight) AS min_tx_weight
   FROM transactions
  WHERE (is_coinbase IS NOT TRUE)
  GROUP BY time_bucket('1 day', "time"), block_id
 );

CREATE MATERIALIZED VIEW coinbase_one_day WITH (timescaledb.continuous) AS (
 SELECT time_bucket('1 day', "time") AS bucket,
    count(*) AS tx_count,
    stats_agg((output_total, output_total_usd) AS stats_miner_revenue,
    min(output_total) AS min_miner_revenue,
    max(output_total) AS max_miner_revenue
   FROM transactions
  WHERE (is_coinbase IS TRUE)
  GROUP BY time_bucket('1 day', "time")
);
  
CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (
 SELECT time_bucket('1 day', "time") AS bucket,
    count(*) AS tx_count,
    sum(fee) AS total_fee_sat,
    sum(fee_usd) AS total_fee_usd,
    stats_agg(fee) AS stats_fee_sat,
    avg(size) AS avg_tx_size,
    avg(weight) AS avg_tx_weight,
    count(
        CASE
            WHEN (fee > output_total) THEN hash
            ELSE NULL::text
        END) AS high_fee_count
   FROM transactions
  WHERE (is_coinbase IS NOT TRUE)
  GROUP BY time_bucket('1 day', "time")
 );

CREATE MATERIALIZED VIEW tick10mcagg WITH (timescaledb.continuous) AS (
 SELECT time_bucket('10m', "time") AS "time",
    symbol
 	avg(price) AS price,
   FROM crypto_ticks
  GROUP BY time_bucket('10m', "time"), symbol
);

CREATE MATERIALIZED VIEW fl10mcagg WITH (timescaledb.continuous) AS (
 SELECT time_bucket('10m', time) AS time,
    symbol,
    FIRST(price, time) AS f,
    LAST(price, time) AS l
   FROM crypto_ticks
  GROUP BY time_bucket('10m', "time"), symbol
);

SELECT add_continuous_aggregate_policy('block_one_day',
  start_offset => NULL,
  end_offset => INTERVAL '1 h',
  schedule_interval => INTERVAL '1 h');

dashboard.json

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": {
          "type": "datasource",
          "uid": "grafana"
        },
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "target": {
          "limit": 100,
          "matchAny": false,
          "tags": [],
          "type": "dashboard"
        },
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "fiscalYearStartMonth": 0,
  "graphTooltip": 0,
  "id": 2,
  "links": [],
  "liveNow": false,
  "panels": [
    {
      "datasource": {
        "type": "datasource",
        "uid": "grafana"
      },
      "gridPos": {
        "h": 2,
        "w": 5,
        "x": 0,
        "y": 0
      },
      "id": 22,
      "options": {
        "code": {
          "language": "plaintext",
          "showLineNumbers": false,
          "showMiniMap": false
        },
        "content": "### Asset: $asset",
        "mode": "markdown"
      },
      "pluginVersion": "9.1.2",
      "type": "text"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 15,
        "w": 19,
        "x": 5,
        "y": 0
      },
      "id": 24,
      "options": {
        "candleStyle": "candles",
        "colorStrategy": "open-close",
        "colors": {
          "down": "red",
          "up": "green"
        },
        "fields": {
          "close": "close",
          "high": "high",
          "low": "low",
          "open": "open"
        },
        "includeAllFields": false,
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": false
        },
        "mode": "candles+volume"
      },
      "pluginVersion": "9.1.2",
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n  time_bucket('$bucket_size', time) as time,\n  FIRST(price, time) as open,\n  MAX(price) as high,\n  MIN(price) as low,\n  LAST(price, time) as close\nFROM crypto_ticks\nWHERE\n  $__timeFilter(time) AND\n  symbol = '$asset'\nGROUP BY time_bucket('$bucket_size', time);",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "open"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "five_min_candle",
          "timeColumn": "bucket",
          "timeColumnType": "timestamp",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            }
          ]
        }
      ],
      "title": "Candlestick view",
      "type": "candlestick"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 5,
        "w": 5,
        "x": 0,
        "y": 2
      },
      "id": 10,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": false
        },
        "textMode": "auto"
      },
      "pluginVersion": "9.1.2",
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "hide": false,
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n  time,\n  price\nFROM crypto_ticks\nWHERE symbol = '$asset'\nORDER BY time DESC\nLIMIT 1;",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "price"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "crypto_ticks",
          "timeColumn": "\"time\"",
          "timeColumnType": "timestamp",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            },
            {
              "datatype": "text",
              "name": "",
              "params": [
                "symbol",
                "=",
                "'$symbol'"
              ],
              "type": "expression"
            }
          ]
        }
      ],
      "title": "Most recent price",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 4,
        "w": 5,
        "x": 0,
        "y": 7
      },
      "id": 18,
      "options": {
        "colorMode": "value",
        "graphMode": "none",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": false
        },
        "textMode": "auto"
      },
      "pluginVersion": "9.1.2",
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "table",
          "group": [],
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n   max(price)\nFROM crypto_ticks\nWHERE\n  time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz\n  AND symbol = '$asset';",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "price"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "crypto_ticks",
          "timeColumn": "\"time\"",
          "timeColumnType": "timestamp",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            },
            {
              "datatype": "text",
              "name": "",
              "params": [
                "symbol",
                "=",
                "'$symbol'"
              ],
              "type": "expression"
            }
          ]
        }
      ],
      "title": "Highest price in (in the time range)",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 4,
        "w": 5,
        "x": 0,
        "y": 11
      },
      "id": 21,
      "options": {
        "colorMode": "value",
        "graphMode": "none",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": false
        },
        "textMode": "auto"
      },
      "pluginVersion": "9.1.2",
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "table",
          "group": [],
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n   min(price)\nFROM crypto_ticks\nWHERE\n  time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz\n  AND symbol = '$asset';",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "price"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "crypto_ticks",
          "timeColumn": "\"time\"",
          "timeColumnType": "timestamp",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            },
            {
              "datatype": "text",
              "name": "",
              "params": [
                "symbol",
                "=",
                "'$symbol'"
              ],
              "type": "expression"
            }
          ]
        }
      ],
      "title": "Lowest price in (in the time range)",
      "type": "stat"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "left",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 29,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unit": "currencyUSD"
        },
        "overrides": []
      },
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 0,
        "y": 15
      },
      "id": 14,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "metricColumn": "symbol",
          "rawQuery": true,
          "rawSql": "SELECT \n  time,\n  price\nFROM tick${bucket_size}cagg\nWHERE time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz\nAND symbol = '$asset';",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "close"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "${cagg:raw}",
          "timeColumn": "bucket",
          "timeColumnType": "timestamptz",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            },
            {
              "datatype": "text",
              "name": "",
              "params": [
                "symbol",
                "=",
                "'$symbol'"
              ],
              "type": "expression"
            }
          ]
        }
      ],
      "title": "Price over time",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 24,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 9,
        "w": 12,
        "x": 12,
        "y": 15
      },
      "id": 17,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "metricColumn": "symbol",
          "rawQuery": true,
          "rawSql": "SELECT\n  time,\n  AVG(LAST(price, time)) OVER(ORDER BY time ROWS BETWEEN (15-1) PRECEDING AND CURRENT ROW) AS \"Moving average\"\nFROM tick${bucket_size}cagg\nWHERE\n  $__timeFilter(time) AND symbol = '$asset'\nGROUP BY time;",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "close"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "one_hour_candle",
          "timeColumn": "bucket",
          "timeColumnType": "timestamptz",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            }
          ]
        }
      ],
      "title": "Moving average (${moving_avg})",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": -1,
            "drawStyle": "line",
            "fillOpacity": 25,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 4,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "always",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "red",
                "value": null
              },
              {
                "color": "green",
                "value": -0.0001
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 10,
        "w": 12,
        "x": 0,
        "y": 24
      },
      "id": 26,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "pluginVersion": "9.1.2",
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n  time_bucket('$bucket_size', time) as time,\n  (last(price, time)-first(price, time)) AS change\nFROM crypto_ticks\nWHERE time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz\nAND symbol = '$asset'\nGROUP BY time_bucket('$bucket_size', time);",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "value"
                ],
                "type": "column"
              }
            ]
          ],
          "timeColumn": "time",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            }
          ]
        }
      ],
      "title": "Price change in USD",
      "type": "timeseries"
    },
    {
      "datasource": {
        "type": "postgres",
        "uid": "6GGihM84z"
      },
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds",
            "seriesBy": "last"
          },
          "custom": {
            "axisCenteredZero": false,
            "axisColorMode": "text",
            "axisLabel": "Change %",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 25,
            "gradientMode": "hue",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineStyle": {
              "fill": "solid"
            },
            "lineWidth": 1,
            "pointSize": 3,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "decimals": 1,
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "red",
                "value": null
              },
              {
                "color": "green",
                "value": 0
              }
            ]
          },
          "unit": "percentunit"
        },
        "overrides": []
      },
      "gridPos": {
        "h": 10,
        "w": 12,
        "x": 12,
        "y": 24
      },
      "id": 12,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom",
          "showLegend": true
        },
        "tooltip": {
          "mode": "single",
          "sort": "none"
        }
      },
      "targets": [
        {
          "datasource": {
            "type": "postgres",
            "uid": "6GGihM84z"
          },
          "format": "time_series",
          "group": [],
          "metricColumn": "none",
          "rawQuery": true,
          "rawSql": "SELECT\n  time_bucket('$bucket_size', time) as time,\n  (last(price, time)/first(price, time)-1) AS change\nFROM crypto_ticks\nWHERE time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz\nAND symbol = '$asset'\nGROUP BY time_bucket('$bucket_size', time);",
          "refId": "A",
          "select": [
            [
              {
                "params": [
                  "change_pct"
                ],
                "type": "column"
              }
            ]
          ],
          "table": "${cagg:raw}",
          "timeColumn": "bucket",
          "timeColumnType": "timestamptz",
          "where": [
            {
              "name": "$__timeFilter",
              "params": [],
              "type": "macro"
            },
            {
              "datatype": "text",
              "name": "",
              "params": [
                "symbol",
                "=",
                "'$symbol'"
              ],
              "type": "expression"
            }
          ]
        }
      ],
      "title": "Price change %",
      "type": "timeseries"
    }
  ],
  "refresh": "5s",
  "schemaVersion": 37,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": [
      {
        "current": {
          "selected": true,
          "text": "BTC/USD",
          "value": "BTC/USD"
        },
        "datasource": {
          "type": "postgres",
          "uid": "6GGihM84z"
        },
        "definition": "SELECT  distinct symbol as name FROM crypto_assets order by name",
        "hide": 0,
        "includeAll": false,
        "multi": false,
        "name": "asset",
        "options": [],
        "query": "SELECT  distinct symbol as name FROM crypto_assets order by name",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "type": "query"
      },
      {
        "auto": true,
        "auto_count": 30,
        "auto_min": "10s",
        "current": {
          "selected": false,
          "text": "6h",
          "value": "6h"
        },
        "description": "Size of the time bucket",
        "hide": 0,
        "label": "bucket size",
        "name": "bucket_size",
        "options": [
          {
            "selected": false,
            "text": "auto",
            "value": "$__auto_interval_bucket_size"
          },
          {
            "selected": false,
            "text": "1m",
            "value": "1m"
          },
          {
            "selected": false,
            "text": "10m",
            "value": "10m"
          },
          {
            "selected": false,
            "text": "30m",
            "value": "30m"
          },
          {
            "selected": false,
            "text": "1h",
            "value": "1h"
          },
          {
            "selected": true,
            "text": "6h",
            "value": "6h"
          },
          {
            "selected": false,
            "text": "12h",
            "value": "12h"
          },
          {
            "selected": false,
            "text": "1d",
            "value": "1d"
          },
          {
            "selected": false,
            "text": "7d",
            "value": "7d"
          },
          {
            "selected": false,
            "text": "14d",
            "value": "14d"
          },
          {
            "selected": false,
            "text": "30d",
            "value": "30d"
          }
        ],
        "query": "1m,10m,30m,1h,6h,12h,1d,7d,14d,30d",
        "queryValue": "",
        "refresh": 2,
        "skipUrlSync": false,
        "type": "interval"
      },
      {
        "current": {
          "isNone": true,
          "selected": false,
          "text": "None",
          "value": ""
        },
        "datasource": {
          "type": "postgres",
          "uid": "6GGihM84z"
        },
        "definition": "SELECT\n\tCASE \n\t\tWHEN '$bucket_size'= '1m' THEN '15'\n\t\tWHEN '$bucket_size'= ‘10m' THEN '15'\n\t\tWHEN '$bucket_size'= '30m' THEN '15'\n\t\tWHEN '$bucket_size'= '1h' THEN '15'\n\t\tWHEN '$bucket_size'= ‘6h' THEN '15'\n\t\tWHEN '$bucket_size'= '12h' THEN '15'\n\t\tWHEN '$bucket_size'= '1'd THEN '15'\n\t\tWHEN '$bucket_size'= '7d' THEN '15'\n\t\tWHEN '$bucket_size'= '14d' THEN '15'\n\t\tWHEN '$bucket_size'= '30d' THEN '15'\n\tELSE '15'\n\tEND as t;",
        "hide": 2,
        "includeAll": false,
        "multi": false,
        "name": "moving_avg",
        "options": [],
        "query": "SELECT\n\tCASE \n\t\tWHEN '$bucket_size'= '1m' THEN '15'\n\t\tWHEN '$bucket_size'= ‘10m' THEN '15'\n\t\tWHEN '$bucket_size'= '30m' THEN '15'\n\t\tWHEN '$bucket_size'= '1h' THEN '15'\n\t\tWHEN '$bucket_size'= ‘6h' THEN '15'\n\t\tWHEN '$bucket_size'= '12h' THEN '15'\n\t\tWHEN '$bucket_size'= '1'd THEN '15'\n\t\tWHEN '$bucket_size'= '7d' THEN '15'\n\t\tWHEN '$bucket_size'= '14d' THEN '15'\n\t\tWHEN '$bucket_size'= '30d' THEN '15'\n\tELSE '15'\n\tEND as t;",
        "refresh": 1,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "type": "query"
      }
    ]
  },
  "time": {
    "from": "now-30d",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "crypto",
  "uid": "K5tvQdHVz",
  "version": 5,
  "weekStart": ""
}