Skip to content

Instantly share code, notes, and snippets.

@lmarburger

lmarburger/daily-transfer-cost.sql

Created November 14, 2013 13:50
Download ZIP

Revisions

  1. lmarburger created this gist Nov 14, 2013.
    6 changes: 6 additions & 0 deletions daily-transfer-cost.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,6 @@
    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;
    26 changes: 26 additions & 0 deletions most-trafficked-with-referrer.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    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;
    12 changes: 12 additions & 0 deletions most-trafficked.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,12 @@
    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;
    9 changes: 9 additions & 0 deletions top-referrers.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,9 @@
    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;