Some queries used with Guardian.

daily-transfer-cost.sql

SELECT '$' || round(sum(bytes_sent)::numeric / 1024 / 1024 / 1024 * 0.12, 2) || ' (' || pg_size_pretty(sum(bytes_sent)) || ')' as cost
FROM requests
WHERE
  time > current_timestamp - interval '1 day' AND
  operation = 'REST.GET.OBJECT' AND
  bytes_sent is not null;

most-trafficked-with-referrer.sql

WITH
  most_trafficked as (
    select coalesce(sum(bytes_sent), 0) as transfer, key
    from requests
    where key is not null
      and time > current_timestamp - interval '1 day'
    group by key
    having coalesce(sum(bytes_sent), 0) >= 1000000000
    order by transfer desc),
  referrers as (
    select key, referrer, count(referrer),
      rank() over (partition by key order by count(referrer) desc)
    from requests
    where referrer is not null
      and referrer not like '%/cl.ly/%'
      and referrer not like '%/f.cl.ly/%'
      and referrer not like '%/api.cld.me/%'
      and key in (select key from most_trafficked)
    group by key, referrer
    order by count(referrer) desc),
  limited_referrers as (select * from referrers where rank <= 5)

select transfer, m.key, r.referrer, r.count
from limited_referrers r
full outer join most_trafficked m on m.key = r.key
order by transfer desc, rank;

most-trafficked.sql

WITH most_trafficked AS (
  SELECT coalesce(sum(bytes_sent), 0) as transfer, key
  FROM requests
  WHERE
    key is not null AND
    time > current_timestamp - interval '1 day'
  GROUP BY key
  ORDER BY transfer DESC)

SELECT pg_size_pretty(transfer), key
FROM most_trafficked
LIMIT 50;

top-referrers.sql

SELECT
  coalesce(substring(referrer from '://([^/]*)'), 'empty') AS host,
  pg_size_pretty(coalesce(sum(bytes_sent), 0)) as transfer,
  count(*) AS requests
FROM requests
WHERE time > current_timestamp - interval '1 day'
GROUP BY host
ORDER BY coalesce(sum(bytes_sent), 0) DESC
LIMIT 50;