nitsHow many sold under a week, under a day? What's the average time difference (in days)?
Updated 2022-02-21
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with sell_details as
(SELECT block_timestamp as bt ,msg_value:sender as addr, msg_value:execute_msg:send:amount/pow(10,6) as amt_sold from terra.msgs
where msg_value:contract = 'terra14z56l0fp2lsf86zy3hty2z47ezkhnthtr9yq76'
and msg_value:execute_msg:send:msg:swap is not NULL
and msg_value:execute_msg:send:contract = 'terra1gm5p3ner9x9xpwugn9sp6gvhd0lwrtkyrecdn3'
),
reward_claim_details as (SELECT block_timestamp, sender,
claim_0_amount+ claim_1_amount as amt
-- , row_number() over (partition by sender order by block_timestamp)
from anchor.reward_claims
where sender in
(SELECT addr from
(SELECT addr, sum(amt) as amt_total,count(*) as times_borrowed_repayed from
(
SELECT block_timestamp as bt, sender as addr,amount as amt from anchor.borrows
UNION ALL
SELECT block_timestamp as bt, sender as addr,amount*(-1) as amt from anchor.repay)
where bt::date > CURRENT_DATE -90
GROUP by 1 )
where amt_total > '0')),
in_a_week as
(SELECT count(distinct addr) as under_a_week_seller_count , avg(time_diff) as avg_time_diff_days
from
(SELECT *,timestampdiff(SQL_TSI_DAY,block_timestamp, bt ) as time_diff from
(SELECT *
from sell_details
where addr in (SELECT sender from reward_claim_details))
inner join reward_claim_details
on sender = addr and timestampdiff(SQL_TSI_DAY,block_timestamp, bt )<7 and timestampdiff(SQL_TSI_DAY,block_timestamp, bt ) >= 0)),
in_a_day as
(SELECT count(distinct addr) as under_a_day_seller_count
from
(SELECT *,timestampdiff(SQL_TSI_DAY,block_timestamp, bt ) as time_diff from
(SELECT *
from sell_details
where addr in (SELECT sender from reward_claim_details))
Run a query to Download Data