Topic: [ANN][AUR] Auroracoin - a cryptocurrency for Iceland - page 33. (Read 506425 times)

select
        (extract(year from block_time) || '-' || extract(month from block_time) || '-' || extract(day from block_time) || ' ' || (extract(hour from block_time)::int / 4) * 4 || ':00:00')::timestamp as t,
        min(block) as min_block,
        max(block) as max_block,
        count(distinct block) as blocks,
        sum(tx_count) as tx_count,
        round(sum(block_minutes)) as block_minutes,
        round(sum(tx_count) / sum(block_minutes), 2) as rate_per_minute,
        round(sum(tx_count) / (sum(block_minutes) / 60), 2) as rate_per_hour
from (
        select
          max(b1.block_height) as block,
          max(to_timestamp(b1.block_nTime)) as block_time,
          round(((b1.block_nTime - prev_block.block_nTime) / 60), 2) as block_minutes,
          count(distinct txout.tx_id) as tx_count,
          round(case when (b1.block_nTime - prev_block.block_nTime) = 0 then 0 else count(distinct txout.tx_id)::numeric / ((b1.block_nTime - prev_block.block_nTime) / 60) end, 2) as rate_per_minute,
          round(case when (b1.block_nTime - prev_block.block_nTime) = 0 then 0 else count(distinct txout.tx_id)::numeric / ((b1.block_nTime - prev_block.block_nTime) / 3600) end, 1) as rate_per_hour
        from block b1
          inner join chain_candidate cc1 on b1.block_id = cc1.block_id
          inner join chain_candidate p_cc on p_cc.chain_id = cc1.chain_id and p_cc.in_longest = 1
          inner join block prev_block on prev_block.block_height + 1 = b1.block_height and prev_block.block_id = p_cc.block_id
          left join block_tx on block_tx.block_id = b1.block_id
          left join tx on tx.tx_id = block_tx.tx_id
          left join txout on txout.tx_id = tx.tx_id and txout.txout_value = 63600000000
        where
          cc1.chain_id = 11 and cc1.in_longest=1 and
          to_timestamp(b1.block_nTime)::date >= '2014-11-25'
        group by
          b1.block_height,
          date_part('hour', to_timestamp(b1.block_nTime)),
          b1.block_nTime, prev_block.block_nTime
        order by
          b1.block_height
) as i
group by t order by t
;
                                                                                                           
select now(), count(*) as claim_count, sum(txout_value) / 1E8 as claim_sum
from txout
inner join block_tx on block_tx.tx_id = txout.tx_id
inner join block b on b.block_id = block_tx.block_id
where txout_value=63600000000 and to_timestamp(b.block_nTime)::date >= '2014-11-25';