Created
May 22, 2023 16:47
-
-
Save mathisve/a5b1e69f2a42b8e72413b65cfb8741a2 to your computer and use it in GitHub Desktop.
Bitcoin 2023 Grafana dashboards
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "annotations": { | |
| "list": [ | |
| { | |
| "builtIn": 1, | |
| "datasource": { | |
| "type": "grafana", | |
| "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": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "drawStyle": "line", | |
| "fillOpacity": 31, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "lineInterpolation": "smooth", | |
| "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": "short" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 9, | |
| "w": 24, | |
| "x": 0, | |
| "y": 0 | |
| }, | |
| "id": 2, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "editorMode": "code", | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n bucket AS \"time\",\n tx_count as \"tx volume\"\nFROM one_day\nWHERE $__timeFilter(bucket)\nGROUP BY bucket, tx_count;\n\n-- CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- sum(fee) AS total_fee_sat,\n-- sum(fee_usd) AS total_fee_usd,\n-- stats_agg(fee) AS stats_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- count(\n-- CASE\n-- WHEN (fee > output_total) THEN hash\n-- ELSE NULL::text\n-- END) AS high_fee_count\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| }, | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Transactions per day", | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "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 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 9, | |
| "w": 19, | |
| "x": 0, | |
| "y": 9 | |
| }, | |
| "id": 4, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "editorMode": "code", | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n bucket as time,\n total_fee_usd / tx_count as avg_fee\nFROM one_day\nWHERE $__timeFilter(bucket)\nGROUP BY bucket, avg_fee;\n\n-- CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- sum(fee) AS total_fee_sat,\n-- sum(fee_usd) AS total_fee_usd,\n-- stats_agg(fee) AS stats_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- count(\n-- CASE\n-- WHEN (fee > output_total) THEN hash\n-- ELSE NULL::text\n-- END) AS high_fee_count\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| }, | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Tx fee in USD", | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 3, | |
| "w": 5, | |
| "x": 19, | |
| "y": 9 | |
| }, | |
| "id": 6, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n max(total_fee_usd / tx_count) AS max_fee\nFROM one_day\nWHERE $__timeFilter(bucket);\n\n-- CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- sum(fee) AS total_fee_sat,\n-- sum(fee_usd) AS total_fee_usd,\n-- stats_agg(fee) AS stats_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- count(\n-- CASE\n-- WHEN (fee > output_total) THEN hash\n-- ELSE NULL::text\n-- END) AS high_fee_count\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Highest fee (in time range)", | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 3, | |
| "w": 5, | |
| "x": 19, | |
| "y": 12 | |
| }, | |
| "id": 7, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n min(total_fee_usd / tx_count) AS max_fee\nFROM one_day\nWHERE $__timeFilter(bucket);\n\n-- CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- sum(fee) AS total_fee_sat,\n-- sum(fee_usd) AS total_fee_usd,\n-- stats_agg(fee) AS stats_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- count(\n-- CASE\n-- WHEN (fee > output_total) THEN hash\n-- ELSE NULL::text\n-- END) AS high_fee_count\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Lowest fee (in time range)", | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 3, | |
| "w": 5, | |
| "x": 19, | |
| "y": 15 | |
| }, | |
| "id": 8, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n AVG(total_fee_usd / tx_count) AS avg_fee\nFROM one_day\nWHERE $__timeFilter(bucket);\n\n-- CREATE MATERIALIZED VIEW one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- sum(fee) AS total_fee_sat,\n-- sum(fee_usd) AS total_fee_usd,\n-- stats_agg(fee) AS stats_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- count(\n-- CASE\n-- WHEN (fee > output_total) THEN hash\n-- ELSE NULL::text\n-- END) AS high_fee_count\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Average fee (in time range)", | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "drawStyle": "line", | |
| "fillOpacity": 38, | |
| "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": "currencyBTC" | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "revenue in BTC" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "unit", | |
| "value": "currencyBTC" | |
| }, | |
| { | |
| "id": "custom.fillOpacity", | |
| "value": 0 | |
| }, | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "yellow", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "revenue in USD" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "unit", | |
| "value": "currencyUSD" | |
| }, | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "green", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 15, | |
| "w": 24, | |
| "x": 0, | |
| "y": 18 | |
| }, | |
| "id": 14, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "editorMode": "code", | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n bucket as \"time\",\n average_y(rolling(stats_miner_revenue) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING))*0.00000001 AS \"revenue in BTC\",\n average_x(rolling(stats_miner_revenue) OVER (ORDER BY bucket RANGE '12 hours' PRECEDING)) AS \"revenue in USD\"\nFROM coinbase_one_day\nWHERE $__timeFilter(bucket)\nORDER BY bucket\n\n-- CREATE MATERIALIZED VIEW coinbase_one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- count(*) AS tx_count,\n-- stats_agg((output_total, output_total_usd) AS stats_miner_revenue,\n-- min(output_total) AS min_miner_revenue,\n-- max(output_total) AS max_miner_revenue\n-- FROM transactions\n-- WHERE (is_coinbase IS TRUE)\n-- GROUP BY time_bucket('1 day', \"time\")\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| }, | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Average miner revenue per block", | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "custom": { | |
| "align": "auto", | |
| "cellOptions": { | |
| "type": "auto" | |
| }, | |
| "inspect": false | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 11, | |
| "w": 24, | |
| "x": 0, | |
| "y": 33 | |
| }, | |
| "id": 15, | |
| "options": { | |
| "cellHeight": "sm", | |
| "footer": { | |
| "countRows": false, | |
| "fields": "", | |
| "reducer": [ | |
| "sum" | |
| ], | |
| "show": false | |
| }, | |
| "showHeader": true | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "editorMode": "code", | |
| "format": "table", | |
| "rawQuery": true, | |
| "rawSql": "SELECT * FROM transactions ORDER BY time DESC LIMIT 10;", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Latest transactions", | |
| "type": "table" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "drawStyle": "line", | |
| "fillOpacity": 51, | |
| "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": "percent" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 14, | |
| "w": 24, | |
| "x": 0, | |
| "y": 44 | |
| }, | |
| "id": 12, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "fe462aa4-427f-410c-8410-8232212e85be" | |
| }, | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "WITH coinbase AS (\n SELECT block_id, output_total AS coinbase_tx FROM transactions\n WHERE is_coinbase IS TRUE AND $__timeFilter(time)\n)\nSELECT\n bucket as \"time\",\n avg(block_fee_sat)*0.00000001 AS \"fees\",\n FIRST((c.coinbase_tx - block_fee_sat), bucket)*0.00000001 AS \"reward\"\nFROM block_one_day b\nINNER JOIN coinbase c ON c.block_id = b.block_id\nWHERE $__timeFilter(bucket)\nGROUP BY bucket;\n\n-- CREATE MATERIALIZED VIEW block_one_day WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('1 day', \"time\") AS bucket,\n-- block_id,\n-- count(*) AS tx_count,\n-- sum(fee) AS block_fee_sat,\n-- sum(fee_usd) AS block_fee_usd,\n-- stats_agg(fee) AS stats_tx_fee_sat,\n-- avg(size) AS avg_tx_size,\n-- avg(weight) AS avg_tx_weight,\n-- sum(size) AS block_size,\n-- sum(weight) AS block_weight,\n-- max(size) AS max_tx_size,\n-- max(weight) AS max_tx_weight,\n-- min(size) AS min_tx_size,\n-- min(weight) AS min_tx_weight\n-- FROM transactions\n-- WHERE (is_coinbase IS NOT TRUE)\n-- GROUP BY time_bucket('1 day', \"time\"), block_id\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Fees & block reward", | |
| "type": "timeseries" | |
| } | |
| ], | |
| "refresh": "", | |
| "schemaVersion": 38, | |
| "style": "dark", | |
| "tags": [], | |
| "templating": { | |
| "list": [] | |
| }, | |
| "time": { | |
| "from": "now-90d", | |
| "to": "now" | |
| }, | |
| "timepicker": {}, | |
| "timezone": "", | |
| "title": "Blockchain", | |
| "uid": "XgXSz18Vk", | |
| "version": 5, | |
| "weekStart": "" | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "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": 1, | |
| "id": 1, | |
| "links": [], | |
| "liveNow": false, | |
| "panels": [ | |
| { | |
| "datasource": { | |
| "type": "datasource", | |
| "uid": "grafana" | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 4, | |
| "x": 0, | |
| "y": 0 | |
| }, | |
| "id": 22, | |
| "options": { | |
| "code": { | |
| "language": "plaintext", | |
| "showLineNumbers": false, | |
| "showMiniMap": false | |
| }, | |
| "content": "## Asset: $asset\n## Granularity: $bucket_size\n### From: ${__from:date:DD/MM/YYYY}\n### To: ${__to:date:DD/MM/YYYY}", | |
| "mode": "markdown" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "type": "text" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 5, | |
| "x": 4, | |
| "y": 0 | |
| }, | |
| "id": 10, | |
| "options": { | |
| "colorMode": "none", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "hide": false, | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "red", | |
| "value": null | |
| }, | |
| { | |
| "color": "green", | |
| "value": 0 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 5, | |
| "x": 9, | |
| "y": 0 | |
| }, | |
| "id": 28, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n last(price, time) - first(price, time)\nFROM crypto_ticks\nWHERE $__timeFilter(time)\nAND symbol = '$asset';", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Price change (in time range)", | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "fixed" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 5, | |
| "x": 14, | |
| "y": 0 | |
| }, | |
| "id": 18, | |
| "options": { | |
| "colorMode": "none", | |
| "graphMode": "none", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n max(price)\nFROM crypto_ticks\nWHERE\n $__timeFilter(time)\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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "fixed" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green", | |
| "value": null | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| }, | |
| "unit": "currencyUSD" | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 5, | |
| "x": 19, | |
| "y": 0 | |
| }, | |
| "id": 21, | |
| "options": { | |
| "colorMode": "none", | |
| "graphMode": "none", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "textMode": "auto" | |
| }, | |
| "pluginVersion": "9.5.2", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "table", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n min(price)\nFROM crypto_ticks\nWHERE\n $__timeFilter(time)\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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "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": 24, | |
| "x": 0, | |
| "y": 4 | |
| }, | |
| "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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time_bucket('$bucket_size', time) as time,\n MAX(price) as high,\n MIN(price) as low,\n FIRST(price, time) as open,\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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "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": "smooth", | |
| "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": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "Moving average" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#70fd7da3", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "Moving average" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "custom.lineStyle", | |
| "value": { | |
| "dash": [ | |
| 10, | |
| 10 | |
| ], | |
| "fill": "dash" | |
| } | |
| }, | |
| { | |
| "id": "custom.fillOpacity", | |
| "value": 3 | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "price" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "green", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 11, | |
| "w": 24, | |
| "x": 0, | |
| "y": 19 | |
| }, | |
| "id": 14, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "editorMode": "code", | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "symbol", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n time,\n price\nFROM tick${bucket_size}cagg\nWHERE $__timeFilter(time)\nAND symbol = '$asset';\n\n-- CREATE MATERIALIZED VIEW tick10mcagg WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('10m', \"time\") AS \"time\",\n-- symbol,\n-- \t AVG(price) AS price,\n-- FROM crypto_ticks\n-- GROUP BY time_bucket('10m', \"time\"), symbol\n-- );\n", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "close" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| }, | |
| "table": "${cagg:raw}", | |
| "timeColumn": "bucket", | |
| "timeColumnType": "timestamptz", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| }, | |
| { | |
| "datatype": "text", | |
| "name": "", | |
| "params": [ | |
| "symbol", | |
| "=", | |
| "'$symbol'" | |
| ], | |
| "type": "expression" | |
| } | |
| ] | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "time_series", | |
| "group": [], | |
| "hide": false, | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n AVG(LAST(price, time)) OVER(ORDER BY time ROWS BETWEEN (35-1) PRECEDING AND CURRENT ROW) AS \"Moving average\"\nFROM tick${bucket_size}cagg\nWHERE\n $__timeFilter(time) \n AND symbol = '$asset'\nGROUP BY time;\n\n-- CREATE MATERIALIZED VIEW tick10mcagg WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('10m', \"time\") AS \"time\",\n-- symbol,\n-- \t AVG(price) AS price,\n-- FROM crypto_ticks\n-- GROUP BY time_bucket('10m', \"time\"), symbol\n-- );", | |
| "refId": "B", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "price" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "table": "crypto_ticks", | |
| "timeColumn": "\"time\"", | |
| "timeColumnType": "timestamp", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Price over time", | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "fixed" | |
| }, | |
| "custom": { | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": -1, | |
| "drawStyle": "bars", | |
| "fillOpacity": 100, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 1, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "always", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "area" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "red", | |
| "value": null | |
| }, | |
| { | |
| "color": "green", | |
| "value": -0.0001 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "percentual_change" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "unit", | |
| "value": "percentunit" | |
| }, | |
| { | |
| "id": "custom.drawStyle", | |
| "value": "points" | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 11, | |
| "w": 24, | |
| "x": 0, | |
| "y": 30 | |
| }, | |
| "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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "editorMode": "code", | |
| "format": "time_series", | |
| "group": [], | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n (fl${bucket_size}cagg.l-fl${bucket_size}cagg.f) AS change\nFROM fl${bucket_size}cagg\nWHERE $__timeFilter(time)\nAND symbol = '$asset'\nGROUP BY time, f, l;\n\n-- CREATE MATERIALIZED VIEW fl10mcagg WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('10m', time) AS time,\n-- symbol,\n-- FIRST(price, time) AS f,\n-- LAST(price, time) AS l\n-- FROM crypto_ticks\n-- GROUP BY time_bucket('10m', \"time\"), symbol\n-- );", | |
| "refId": "A", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "value" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| }, | |
| "timeColumn": "time", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| }, | |
| { | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "format": "time_series", | |
| "group": [], | |
| "hide": false, | |
| "metricColumn": "none", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n ((l/f)-1) AS percentual_change\nFROM fl${bucket_size}cagg\nWHERE $__timeFilter(time)\nAND symbol = '$asset'\nGROUP BY time, f, l;\n\n-- CREATE MATERIALIZED VIEW fl10mcagg WITH (timescaledb.continuous) AS (\n-- SELECT time_bucket('10m', time) AS time,\n-- symbol,\n-- FIRST(price, time) AS f,\n-- LAST(price, time) AS l\n-- FROM crypto_ticks\n-- GROUP BY time_bucket('10m', \"time\"), symbol\n-- );", | |
| "refId": "B", | |
| "select": [ | |
| [ | |
| { | |
| "params": [ | |
| "price" | |
| ], | |
| "type": "column" | |
| } | |
| ] | |
| ], | |
| "table": "crypto_ticks", | |
| "timeColumn": "\"time\"", | |
| "timeColumnType": "timestamp", | |
| "where": [ | |
| { | |
| "name": "$__timeFilter", | |
| "params": [], | |
| "type": "macro" | |
| } | |
| ] | |
| } | |
| ], | |
| "title": "Price change in USD", | |
| "type": "timeseries" | |
| } | |
| ], | |
| "refresh": "5s", | |
| "schemaVersion": 38, | |
| "style": "dark", | |
| "tags": [], | |
| "templating": { | |
| "list": [ | |
| { | |
| "current": { | |
| "selected": false, | |
| "text": "BTC/USD", | |
| "value": "BTC/USD" | |
| }, | |
| "datasource": { | |
| "type": "postgres", | |
| "uid": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "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": false, | |
| "auto_count": 30, | |
| "auto_min": "10s", | |
| "current": { | |
| "selected": false, | |
| "text": "1m", | |
| "value": "1m" | |
| }, | |
| "description": "Size of the time bucket", | |
| "hide": 0, | |
| "label": "bucket size", | |
| "name": "bucket_size", | |
| "options": [ | |
| { | |
| "selected": true, | |
| "text": "1m", | |
| "value": "1m" | |
| }, | |
| { | |
| "selected": false, | |
| "text": "10m", | |
| "value": "10m" | |
| }, | |
| { | |
| "selected": false, | |
| "text": "30m", | |
| "value": "30m" | |
| }, | |
| { | |
| "selected": false, | |
| "text": "1h", | |
| "value": "1h" | |
| }, | |
| { | |
| "selected": false, | |
| "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": "ef32eb16-e3ae-4fcd-a5cd-5d3fc51ff4ec" | |
| }, | |
| "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-2d", | |
| "to": "now" | |
| }, | |
| "timepicker": {}, | |
| "timezone": "", | |
| "title": "Crypto ticks", | |
| "uid": "uhrysK84k", | |
| "version": 9, | |
| "weekStart": "" | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment