Buffer emoji updates count

emoji_count.sql

DROP TABLE "public"."emoji_table";
CREATE TABLE "public"."emoji_table"
(
       	"emoji" VARCHAR(70)
       	,"meaning" VARCHAR(160)
)
DISTSTYLE ALL;

--got this file from http://stats.seandolinar.com/wp-content/uploads/2015/04/emoji_table.txt

copy emoji_table from 's3://buffer-data/emoji_table.txt'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=xxx'
delimiter ',';

select * from emoji_table
limit 10;

--Top 50 emojis
select
    emoji,
    to_char(count(distinct id), '999,999,999,990D')
from updates,emoji_table

where sent_at > dateadd(day, -30, current_date)
and text like '%' || emoji_table.emoji || '%'
group by 1
order by 2 desc
limit 50;

--Emoji updates and total
select
    count(distinct id)
from updates,emoji_table
where sent_at > dateadd(day, -30, current_date)
and text like '%' || emoji_table.emoji || '%'
limit 50;
--417609

select
        count(distinct id) total
    from updates
    where sent_at > dateadd(day, -30, current_date);
-- 5616244

select 417609.0 / 5616244;