Vault activity query on Numia

vault_activity

WITH
  vaults AS (
  SELECT
    vault
  FROM
    UNNEST(['dydx1esh4d4rdl58lqauha8met8g78xry52ry3nnmw6', 'dydx1cnd3ftpvqr3khjsfn6ptz0a8fzd99p4dnmkt2l', 'dydx165fla2xss0wrg8rr04x3e46mneat4fckrcf429', 'dydx16fg2j83kkzwz9ghqrdgatzsnsfz7mzknz0e8yw', 'dydx1ge5qgepfxcjetkgvfgcph05zcv78we53w2g5h7', 'dydx12dffextgdnvrv349krs2thvhcdnwr4850d35h7', 'dydx19ykejm094kzj054wt3lks3fk9kuwfu9h0z8hcs', 'dydx10vwegs2dyjm6443uqpp0y7v9u4snmcnu5wpgnr', 'dydx1qsyv2ug8y0m5xgapfr6avrj9ktu79pyq49pphg', 'dydx1l04nf4udw7swyrzge66j66n09ry4aawfm4xd07', 'dydx1kywd7tcyvpwx8j6dc7t5733wmum4k2dq68trn6', 'dydx1m0pn0ygh37qp345rd44scq58ch7e4h0wfpdrcs', 'dydx1vvdpmwsyum9r5y92c3gpks5ednxqazfmpjv8qf', 'dydx1rtkg76uvpll6jhvm79knsrqaf9vx48wcfn7she', 'dydx186d03739dhkt8eeqahql3dcscyvgh9f6gsuzxn', 'dydx1c5atsq350zsdwzld4e9k648cx4rkr9792e4xtp', 'dydx16hklzgswcw9w4xhh4vdajlclaghwzgmr2m6hlp', 'dydx1c6s0pg7hsc9enyr4nn9vhmyk8yps3wkhvlr0fz', 'dydx1y73m286vcnvrjnrcwv7d7l4gs2v2x5r65n7726', 'dydx1ypya4270fm4gujvktk67ldvru3y2nx7pvud7ej', 'dydx1mt66ymx2vv7dzct3t0h6m3afln4s235jf0esja', 'dydx127pntl3k27tfqaqs2f6nqu6vcndfnamt7ylv0c', 'dydx15t8hzcu3exjnh7lp4ks2m0qzcpfmjw7wnq0fvr', 'dydx1key68rph9mtmm09f5udr58pfyq586dqr56zusw', 'dydx1l3avcq2mwy9w0zua5m6g9j258f2edr5um74fwy', 'dydx1vpdmlvk8dg6wsqwp0am3t89wzt6c6zdve3qjty', 'dydx1p56afvyvjaktnnnskqvr4k38jdhr86m4p5dpmu', 'dydx15ww9np5agf9zfkpxcy89xemh3hrz2v34vr9xkr', 'dydx1pcf5zp2qek6satxnu76hshs8veg4z3f5enj9rd', 'dydx1jyukv56r5hv87t5ked8tpy0haetlclnzncjtg3', 'dydx18jel3thzvshjm0ud0rav77gessj2dhh35qzz0d', 'dydx1ev45egc7c2har2jxf6lxwsmtldk295x4rucflq', 'dydx1jqsjfnwwhuey2wfp7le4fa8dgnjxh5p6d5lxh9', 'dydx1ecrdl42227qfs7tr9h94k8mfxl6zfsysx3457j', 'dydx19seeflkz7rlhxzflc4p8qjnem620jaw655vh7v', 'dydx10kgkxtwtue28hjgtmflhxltwezrc5aw4yuctu0', 'dydx1ag5jgl436uwa007t6ugvnwvy8hj0ynvvvtcu2g', 'dydx1w9nquxp466tmy58ln69wfv6lwhk9xzaz9lzfkv', 'dydx10aysrtwzlasf4wavv6tgp40mpkn0vl3qcc0gcq', 'dydx1xe7h0k8qqyupu5z7xh83wpd79d9h0s4w6eukre', 'dydx18ndf2h65c3qma5av354nz6kcjdkv5wtvsmj7z4', 'dydx1wrt4ytendg0l490m028kt5c0850fkkmjwf5tg4', 'dydx1snpcs7pgxfaw6yrtth3gajequ7f9zckp8zltjg', 'dydx1czwrmjdehcvmk4xjdg8mwkxecgg67am0ccxdul', 'dydx1mcskurn6epk8av0whh8rjtjafhs5j7dw4za9us', 'dydx1ek2uc2sw25w633fyxhqfkgt335fd4l6hkpvw3j', 'dydx1grnsunqr2dhtgld5vcwu0awzntk88sq9fngz35', 'dydx144wyzc7zfgtmd4rm5l5vs5x0cfmkm7j00uh492', 'dydx1tztgq6lygfvdd94ascze3phunclt5k3r7dc0le', 'dydx1evmk2a3rp6adr5nzwsjh956deps30k08tchugs', 'dydx190wezyn99zsk2hgc3s7nlt5sjltzj04cm657hl', 'dydx1v2p6qj9lvzs46nmyjxw26lwaqa4g2rd4jq9yer', 'dydx1dc75mue9ylew9ql7yzgn5p4utgwvct747q6zym', 'dydx1s55fuhygskjyu2llrvtaqqzuycl9dkvtrh4vs3', 'dydx1ulquskxxqs80lq4s4q78f6g8swu0hr74ap0c27', 'dydx1gh7j9n3hh59zm3r4zhfk69nydnz723yf2gmujm', 'dydx1pjvu4hurzc87l2j887f56s5qqk7dwsapdssvc4', 'dydx1jnsphepypg3xczftcxlg8fmlr0djkhjnmxnd4m', 'dydx1264ag2trux63698h56vduy566yw5qkrlks3e3f', 'dydx14wgrzzurfygjspmrptgy64x8v79q709her47lt', 'dydx10fxlw7umtqdel7n4tx8nrzwtzy4dpev6ls3na0', 'dydx1pzy946s6urmt85jpqj830fduvmj0zwu08nd8wq', 'dydx1ushp8haqknjw3ur57ddyfhgsmcq0xysmn6m8ex', 'dydx1ffwcwp4e7cgll5qyv0z685hw8l5l3q7jp4q2ks', 'dydx1eldxlhm8ass4fcm2s9yvhf5qguy6p5m63tvjkf', 'dydx1clduy6jtmju4chl4mfuv0zv6nryhpp07uuumr9', 'dydx10ha4kaph74uxsstw547ux8hh6yk7vv7xgykxtl', 'dydx1peve5pgycz6lx5r3jukaqxxr5tqyyl6xs7j09l', 'dydx1dgrakcus0zwyajzprkfjnf666cnwcdew9zvvly', 'dydx13kcp8ljcef243yv9f2js6nr7uj4yasv5xsk4gy', 'dydx1ekgna52r076ng7d69c3u8ct7r4k3hw955nedwg', 'dydx1la0t02ztyfch3y42lgp43cfxgjxp7gmghsxun5', 'dydx12laptmp99yttf5k7s0uzqer6cj44u3ynj60sx3', 'dydx1p9artkuehv25dyavygfa0amp90qeqaz9j4xn94', 'dydx1lkxwl4hdwn2ggt9q2ruuh4nmrhj5hk9m3c73kt', 'dydx13zuj68f5jauk5f4fkejpxpqnqaeqygllvpmumx', 'dydx199sr4gnmllvrkyr834gyg8m8d27n07509dkx43', 'dydx17ps237qx5v2gfp7lqx263qgzq7jd3570vwr7ry', 'dydx1jatnxzqghex6f924c0h5j5sqagczqh5j48tp84', 'dydx1hmhzwq5cn86u00q33kyae6xmljwmdgne2h5akp', 'dydx1x095fjmdaeh20t8juc0hmswdzmm65ugcsdhp4x', 'dydx1j62ts3d47m6x9005ljgmxrpxwaxx84a7q9874z', 'dydx1ht8hrec0tupp00q8afkfn6lzzxrdcmlp8knwyz', 'dydx1gu0pqrxqucdxrdqxwcts5tpnl855anv76ramnh', 'dydx1ymaawj692v97k6tfz8mpelxnanl7zgwnzzegmq', 'dydx10pmmpjllyrap2cthfy49axfh8erlw87c8qzt92', 'dydx1tpnycqu4svhpv46p6uayqvqdmckdzmt83dkh0s', 'dydx1g5dpm5kecfmtn4uzp5e55k0hhzynqjmdg4n2ul', 'dydx16q2pw8q9mfdva7zqakfvt38n7qasna38fsf7ak', 'dydx15wy09dd8zfp5z2hkh90jwynve2awk6v8w2ztjg', 'dydx1g460jgzrc6dy9a8ptn5d3mey0nqasvhdyl6ec4', 'dydx1g99yaj7339hfu5a80z6yqj7gtssr884g6zkd8x', 'dydx1qyw5mnsgunyutyhg603gl2wwehd2zy5tcwnrpv', 'dydx14a4sg762e694e9j5pttpp3gld6j4hyq53u6jps', 'dydx1fhz38ll7xugk3lfgrn9hky6x0knjw7xxvtca85', 'dydx1h8n2lwx8pzk509cxcd2l0ngvzutrsqrfr42nxk', 'dydx1xj8m8jkaqfzgjrpa570n96hyt74c0066yl2ar5', 'dydx1qm5ryzt33gjjhy7h9apxm8889un7hepw7utg4j', 'dydx1muttrndpjec7j2jv4je055z2mvhc3dm2xz524a', 'dydx1utsgenwxx6hw04fugte69y2cyzdngm0a0dhqc6', 'dydx1a5c5yk88zcax7s3dzt72lrrwex6efnfmweycvs', 'dydx1kqpncwy79jh0qwfzaffzdnk9nyngel0xz48t9f', 'dydx1pzaql7h3tkt9uet8yht80me5td6gh0aprf58yk', 'dydx124wtevhqpjg35m9ph7zf03al55k4a460celcn9', 'dydx16ej3jztl5wqhwegsys6y0zkldzj2m984c9066n', 'dydx1r2gfpy5kcr74vve5duat36hygr53f6qp0n7asm', 'dydx1lxg3qjp5xesysr6ceh0rq2zzah6xe7qv2lt0xk', 'dydx169s7gl0a2v74zhqhmluljdx2dpkmpc9pp4snk7', 'dydx1ddhw6s8jdd6lhqx7drs75hkcv4gfht4wh97rn2', 'dydx17aszlxyfvtw6rg2l2s38e4t7dqnn6mwdx4mg7w', 'dydx1krtpfeau4p4n8ffs3wlc0kj63pgx70lxnk3cpd', 'dydx1747mf6l99pqps9gv6tgvfr5wxuvny7ucalr33k', 'dydx14rplxdyycc6wxmgl8fggppgq4774l70zt6phkw', 'dydx1c0m5x87llaunl5sgv3q5vd7j5uha26d2q2r2q0', 'dydx1yzqutt4f6wq74sfy7j45s9gfmlxcy7vy3cu9jv', 'dydx138aqldklse4uedzesdfkvhqh399n8gpa8rmzvq', 'dydx16nadwv208kckr5tac033z6eslgy03plkhdmgcq', 'dydx162a22u824nkyhkpxq6nl90p0z6s87jcsdg2339']) AS vault )
SELECT
  SUM( ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums ) / 1000000 AS total_volume,
  SUM(
    CASE
      WHEN m.maker = v.vault THEN m.taker_order_fee_quote_quantums
  END
    ) / 1000000 AS taker_fees,
  SUM(
    CASE
      WHEN m.taker = 'dydx14dltc2w6y3dhf0naz8luglsvjt0vhvswm2j6d0' OR m.maker = 'dydx14dltc2w6y3dhf0naz8luglsvjt0vhvswm2j6d0' THEN ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums
  END
    ) / SUM( ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums ) AS mm_a_share,
    SUM(
    CASE
      WHEN m.taker = 'dydx18p7nz5rqezkyscdz9pv9rchnsesjyjjyfe92t3' OR m.maker = 'dydx18p7nz5rqezkyscdz9pv9rchnsesjyjjyfe92t3' THEN ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums
  END
    ) / SUM( ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums ) AS mm_b_share,
    SUM(
    CASE
      WHEN m.taker = 'dydx15u3dtsf4twdxttvy7850dkex7tcf3ps2y8wcuf' OR m.maker = 'dydx15u3dtsf4twdxttvy7850dkex7tcf3ps2y8wcuf' THEN ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums
  END
    ) / SUM( ABS(m.maker_quote_balance_delta_quote_quantums) + m.maker_order_fee_quote_quantums ) AS mm_c_share
FROM
  vaults v
LEFT JOIN
  `numia-data.dydx_mainnet.dydx_match` m
ON
  m.maker = v.vault
  OR m.taker = v.vault