https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-docker.html

docker run -d -p 172.30.18.235:3000:3000 --name metabase metabase/metabase

http://172.30.18.235:3000

first example: hackernews

caching maybe not necessary?

# hn aggregation

with hn_data as (
  select * from hackernews_ask_hn 
  union 
  select * from hackernews_best
  union 
  select * from hackernews_new
  union 
  select * from hackernews_show_hn
  union 
  select * from hackernews_job
  union
  select * from hackernews_top 
)

select 
  'https://news.ycombinator.com/item?id=' || id as item,
  to_char(time, 'YYYY-MM-DD HH24:MI') as time,
  score,
  descendants,
  title, 
  url
from hn_data 


{{snippet: Hacker news aggregation}}

where title ~*
  case 
    when {{ TitleRegex }} = 'any' then ''
    else {{ TitleRegex }}
  end
order by score desc 

# aws cost by service daily

select 
  service,
  to_char(period_end, 'YYYY-MM-DD') as day,
  blended_cost_amount
from aws_cost_by_service_daily 
where {{ aws_service }}
order by day 

# aws anomalous service cost

with services as (
  select distinct
    service
  from aws_cost_by_service_daily
),
averages as (
  select 
    service,
    avg(blended_cost_amount) as daily_average_cost
  from 
    aws_cost_by_service_daily a
  join
    services s 
  using (service)
  group by service 
),
yesterday as (
  select 
    service,
    blended_cost_amount::numeric as yesterday_cost 
  from 
    aws_cost_by_service_daily a
  join
    services s 
  using (service)
  where a.period_end > now() - interval '2 day'
),
combined as (
  select
    a.service,
    a.daily_average_cost::numeric,
    case 
      when y.yesterday_cost is null then 0 
      else y.yesterday_cost
    end as yesterday_cost
  from 
    averages a 
  full join 
    yesterday y 
  using (service)
  where y.yesterday_cost > 0
  order by service
 )
 select 
   c.service,
   round(c.daily_average_cost, 1) as daily_avg_cost,
   round(c.yesterday_cost, 1) as yesterday_cost,
   round((c.yesterday_cost / c.daily_average_cost) * 100::numeric, 1) as pct_yesterday_above_avg
 from 
   combined c 


with data as (
  {{snippet: AWS daily costs by service: avg, yesterday, % change}}
)
select * from data 
where pct_yesterday_above_avg > 500 
or yesterday_cost > 10