Rebase pipeline branch off development

1 files changed, 87 insertions, 0 deletions

diff --git a/packages/pipeline/src/scripts/query_data.ts b/packages/pipeline/src/scripts/query_data.ts
new file mode 100644
index 000000000..97e3749ea
--- /dev/null
+++ b/packages/pipeline/src/scripts/query_data.ts
@@ -0,0 +1,87 @@
+import { formatters } from '../utils';
+export const dataFetchingQueries: any = {
+    get_missing_txn_hashes: `
+        SELECT
+            a.txn_hash
+        FROM
+            events_raw a
+        WHERE NOT EXISTS
+            (
+                SELECT
+                    *
+                FROM
+                    transactions b
+                WHERE
+                    b.txn_hash = a.txn_hash
+            )
+        AND
+            a.block_number >= $1
+        AND
+            a.block_number < $2`,
+    get_used_block_numbers: `
+        SELECT DISTINCT
+            a.block_number
+        FROM
+            events_raw a
+        WHERE NOT EXISTS
+            (
+                SELECT
+                    *
+                FROM
+                    blocks b
+                WHERE
+                    b.block_number = a.block_number
+            )
+        AND
+            a.block_number >= $1
+        AND
+            a.block_number < $2`,
+    get_token_registry: `
+        SELECT
+            *
+        FROM
+            tokens`,
+    get_max_block: `
+        SELECT
+            MAX(block_number)
+        FROM
+            events_raw`,
+    get_relayers: `
+        SELECT
+            *
+        FROM
+            relayers`,
+    get_most_recent_pricing_date: `
+        SELECT
+            MAX(DATE(timestamp))
+        FROM
+            prices
+    `,
+    get_top_unknown_token_addresses: `
+    SELECT a.token_address as address, a.txn_value / 2 as total_txn_value
+FROM
+(SELECT token_address, SUM(txn_value) as txn_value
+FROM
+(select a.timestamp, a.maker_token as token_address, (CASE WHEN a.taker_txn_usd_value > a.maker_txn_usd_value OR a.maker_txn_usd_value IS NULL
+                        THEN a.taker_txn_usd_value
+                        ELSE a.maker_txn_usd_value END) as txn_value
+        from events_full a
+        where a.event_type = 'LogFill'
+        and a.timestamp > (NOW() + INTERVAL '-24 hours')
+        union
+        select a.timestamp, a.taker_token as token_address, (CASE WHEN a.taker_txn_usd_value > a.maker_txn_usd_value OR a.maker_txn_usd_value IS NULL
+                        THEN a.taker_txn_usd_value
+                        ELSE a.maker_txn_usd_value END) as txn_value
+        from events_full a
+        where a.event_type = 'LogFill'
+        and a.timestamp > (NOW() + INTERVAL '-24 hours')) token_txn_values
+WHERE token_address IS NOT NULL
+AND txn_value > 0
+GROUP BY 1
+ORDER BY 2 DESC) a
+LEFT JOIN tokens b
+ON a.token_address = b.address
+WHERE symbol is NULL
+ORDER BY 2 DESC
+`,
+};