flipside

    open ordex
    MLDZMNopen ordex
    Updated 2024-02-02

    with price_tab as (
    SELECT date_trunc('day',hour) as day1,
    avg(price) as btc_price
    FROM bitcoin.price.ez_hourly_token_prices
    group by 1 ) ,

    open_ordex as (
    select
    transactions.block_timestamp,
    transactions.block_number,
    transactions.tx_id,
    transactions.fee,
    input1.pubkey_script_address AS buyer,
    input3.pubkey_script_address AS seller,
    outputs[2]:value AS amount
    from
    bitcoin.core.fact_transactions transactions
    join bitcoin.core.fact_inputs input1 on transactions.tx_id = input1.tx_id
    join bitcoin.core.fact_inputs input2 on transactions.tx_id = input2.tx_id
    join bitcoin.core.fact_inputs input3 on transactions.tx_id = input3.tx_id
    join bitcoin.core.fact_inputs input4 on transactions.tx_id = input4.tx_id
    where
    transactions.block_number > 767429
    and transactions.block_timestamp > '2023-02-01'
    and input1.value = 1000/100000000
    and outputs[3]:value = 1000/100000000
    and outputs[4]:value = 0.000006
    and input3.pubkey_script_address = transactions.outputs[4]:scriptPubKey:address
    ),

    sales as (
    select
    distinct tx_id,
    block_timestamp,
    to_varchar(buyer) as buyer,
    Last run: over 1 year ago
    TX_ID
    BLOCK_TIMESTAMP
    BUYER
    SELLER
    AMOUNT
    AMOUNT_USD
    FEE
    FEE_USD
    1
    		0d37ccbbf77afdce3c5ac57570403d2052fab733306dd2f0249faa83da40ad072023-12-07 22:05:42.000bc1pj6twrhh30dthxxw8flf0d9kqah6rwc6ysdamftuz5nf99k0kh80q3p46jg3PyosYnSvJcQJqVo2DJMbXq9zYU6q2yUMV
    0.000408
    		17.7722930.000293512.784725479
    2
    		a77e2babb8efed79e91f279a181e4a513715dbebac3b6956cdcf3e0c6ef5360a2023-12-07 07:17:17.000bc1pa6903hdgn858gg5gph0x6z98uj3nwpcy0hfgq8k60n3ey96eyw8selc2qqbc1p0j7s7xvtyk6gptak5a84sq5tc4s97nacldc27s9ppeyzew0pfjfqfde6z7
    0.000408
    		17.7722930.00033814.723125083
    3
    		0f17bfb28ead5df2270b45db55cbf5bebdb4596251f44a49f9cae62e5559759a2024-01-09 01:14:35.000bc1pc2qhdxswu2c6vznjljfex4c7a0u35ue92aczl9k433mu3a3ayyesx8c2gebc1psj30xcujdxuuf9wwnast2pep2w66pwz0mcm74jyp2fg74kc9tyvsqnd63m
    0.000408
    		19.0428560.0003358215.67395074
    4
    		2be22cefc30a20412c2c627ab81d7f37628c3835b06993532e5384b279551fc72024-01-29 18:28:19.000bc1pq0pl2s39qmfgvt2eq5l84gyv26ue202e7p4jvy5lphxuc8dufcuqxkewh935L6bdEpeWMG65dTTwxGFuZQGrteN2T3rB
    0.000408
    		17.3278790.0002423410.292250482
    5
    		30052c52ff3a2be9fc4d8cafd1aaeac4a9eb46b7e05e8e77fc58fd40778913ad2024-01-06 19:15:40.000bc1p5k780nvc358mar3d4ue99hpfczrx0rq2aqmgwehpm6h4q6u6efzstquled32ECWjgYvqqEkpGVjZiT6wUXYcMZyqg7f2
    0.000408
    		17.8873490.0003410414.95171937
    6
    		3de45d82b1d61796c2c5326a7df8394f7afa713f0bd07d8cb7522787b92a38ef2023-11-30 05:50:19.000bc1pmz6glu85gx0p57gmjeqm00sy568t2u6ys90gkt33jwruydx7uhdqw9sdhh36bkEv7PcDZKL1RVkwpaum31ar9ZNFQyaV
    0.000408
    		15.4151750.0002764810.446048
    7
    		5e176ea173edcc5d4d8ff778d69888154306a9dc2ddd7d4c5dd85c8dfc76f8a02024-01-15 09:45:54.000bc1p8fl72f7chpx64gu3u25wu3zp5k65npqms0upl0496778ar3jwe2qhhgcq03Hac5hqn6RwN7G5A36xmoY5cCd9wMAqYGd
    0.000408
    		17.3883987740.0004327518.443209729
    8
    		4dae086a97e153053c0df889db9d5d3986d14ccd94239c818b9450fb87acecb52023-12-26 14:23:50.000bc1p67wtl7wk5nzxldk23wge0kuee2jvwkq4rkqq6cvzjt6sghapzy7skufg6r3AmYSDFWkrX6bS4aGAaqgXscLxhJtQBkkJ
    0.000408
    		17.4158056730.000862436.812232382
    9
    		a9c2112c8cddda5e993d00ee293f73f4605a48acae23e7f2899724c58707bd0e2023-12-09 01:24:30.000bc1p2cpp64eh3fhshyq4m7jpn6dss3ms2t5eylaldj2hm8sur9wkekystx59783Gih4QcZQKAkmbM2DAaS61qthFtF2oZPuP
    0.000408
    		17.9709020120.0002289310.083525974
    10
    		d9b275eb194bbc3eb5a93eb3db12c2b111db16fa5d625048e062fec613e13c922023-07-18 19:13:09.000bc1pg3lf85lqz4jh0hadftd74na6gj8dkaeg029ex9g0k2p76568hrtq6rew8v39wRAAk7MoVC6MEX1d7wWwdS5y8UuD8V8t
    0.000408
    		12.2236290.000046081.38055104
    11
    		c73f7dc0340a3040c90f130e38716296d4e62c8bedb70e855c6152557fc6537f2024-01-23 04:20:26.000bc1pfdrmvr4rv08spujdypedvv4h3mnyvkaglt5pctffv54g9rnhwwgs3764rtbc1qfclzwmjshjauhvznz2hjw8matnrprgdxru4nr3
    0.000408
    		16.0814390.000176056.939062098
    12
    		0bff186f9bcfc9062d25b50381077657c77cc3f1f076d074f9c7cda18564d66c2024-01-09 23:15:36.000bc1pxmcqt6cways93c64evs70l3dvw3nwkg0fvn4amhn4pctyd4pkxhqrrlvdp36u5M6sjW77muSfJmDz9JgcDGESGpCeCyY
    0.000408
    		19.0428560.0003139614.653664387
    13
    		12ff6746441325b987a9c9a1b33ac995aa2fa9a6d35fad71f215001f2f5a064a2024-01-27 20:08:34.000bc1p3wsk2p98fkcym0tdjxlwyt6x33szwzt55wp4nhl6kj6u76h7xpsqmpn62l3CHhuwSCC2GnTW8ifZALoX7aDzmjMmdcie
    0.000408
    		17.0621860.000225039.410548323
    14
    		201ed16c860ea6a09317bc832864cee6777c271563f12546eb28a4d87746f8b72023-12-07 13:11:33.000bc1pzrerc7trd0x434kkqck8sadprzp7rckmwl90f4a5sv6lxmcda5wsq5xl7c3DtHz9sTAKJfm3Hpp8pdwuDjrHy9aCJqG4
    0.000408
    		17.7722930.0015379266.99109032
    15
    		007f02876e6f0c77a20a2c104ecca4b69f731d8ab5848ffcc48d9b46efbf09a42024-01-06 19:23:06.000bc1p8rksq8eayksuvc0g2ncca3zq42mfswkk20hs7dpnejelpxaljdqqtjapj03JMT3SV2xg8U7PGaaQhJ5EPQCribwrCXgn
    0.000408
    		17.8873490.0002596511.383456294
    16
    		3da3b96de68e9242376d6917ea961c6b00a791d3d511bf439ace59e6cca883022023-11-30 04:28:30.000bc1pm6pl64rvgcueussuz4x3xffgtjpa4r0slakm0gz0ja6gzww9m9xst00pzubc1p8gkfd7edneskeq33a6mq2s557l355waptdc6l9ef5mz8ax4sw83qlkzve9
    0.000408
    		15.4151750.00024449.233992083
    17
    		4017469a83170a0cf1f9183c4bfa9a19160a6298630025747acb93fd26c1428b2024-01-09 18:44:57.000bc1p5z5jmkfgtpnq5e6j46tp8nzwyqadq0sn46gfes7r50qw3yeszk7sevc2fk3HVKoFY3Yc1RLXuFDdL2a3Ab5LfrSYTYgp
    0.000408
    		19.0428560.0002423411.31089638
    18
    		df4512a36ba1659ecd07eadc4ce7be247031f8aa2dac2ef3199689f731d402a82024-01-13 08:35:51.000bc1p4see2zf8ntq7aw73r4xfljykcwwdzrptspvqvy49ldngna02me8q3gpsyh3C2hBrxeL34Dro7q9KCueWoFZbeHe1T15z
    0.000408
    		17.4886645660.0002481110.635079817
    19
    		17342c1db52e08d8172f71cdcd288b9500e96c6d127b4c03150af137cfbecd762024-01-05 18:47:04.000bc1ptvtx2lls620rggq5uq4sc6q6z98ndjf8jyzftzkq7jm6l2lp0svqkd6u6mbc1qy6hdmx2usf4gnv7r45072jmhfx5yk5uslseza7
    0.000408
    		17.8610160.0002312410.12299348
    20
    		40b2d5ca4f3d09a2bd2f3dda1958395e85e199bc6ef17c6802d6de67f554e1e42024-01-04 22:39:00.000bc1pzx4s4xkj4ed4xjpsewtyxxax3uwdpv2z39gkuaw3vyfd03lhny9s5acyj93BcVywb9SuHuDf7F4Yd4EEA46dQccyhgVL
    0.000408
    		17.7514510.0003058113.305321643
    ...
    192
    46KB
    164s