selection.sql

# Quick Hit stories in the last 24 hours
select count(created) 
from articles_ddarticle 
where age(modified, created) < interval '1 hour'
and age(localtimestamp, created) < interval '1 day'
limit 50;

# Biggest social hits, last 72 hours
select slug, total_shares
from articles_ddarticle 
where age(localtimestamp, created) < interval '3 days'
order by total_shares desc
limit 50;

#Total articles by author
select d.last_name, d.first_name, count(a.id) 
from articles_ddarticle a, author_profiles_author_profile b, articles_ddarticle_authors c, auth_user d
where a.pub_status='P'
and age(localtimestamp, a.created) < interval '30 days'
and a.id=c.ddarticle_id
and c.author_profile_id=b.id
and b.user_id=d.id
group by c.author_profile_id, d.last_name, d.first_name
order by count(a.id) desc;



# Same as above, with headlines.. requires json to parse result set
select slug, headlines, total_shares
from articles_ddarticle 
where age(localtimestamp, created) < interval '3 days'
order by total_shares desc
limit 50;

# Quick Hit stories in the last 24 hours
select count(created) 
from articles_ddarticle 
where age(modified, created) < interval '1 hour'
and age(localtimestamp, created) < interval '1 day'
limit 50;

# Possible "ready for edit" number. May be inaccurate. Count posts in draft status created < 24h ago
select count(id) 
from articles_ddarticle 
where pub_status='D'
and age(localtimestamp, created) < interval '1 day';

#total published articles, last 24h
select count(id) 
from articles_ddarticle 
where pub_status='P'
and age(localtimestamp, created) < interval '1 day';