Question: Suppose we have a data-stream feeding in once per minute and we want to keep a running track of the top 5 values over the previous 10 minutes. Intuitively there should be some queue solution to this but I'm struggling to find an elegant way due to the fact that an element can be popped for two different reasons (it look place 11 minutes ago or <10 minutes ago but 5 higher values have been seen since)

Any suggestions would be appreciated!

Answer: 
This is probably simpler using correlated subqueries:

```
select p.*,
       (select count(*)
        from comments c
        where c.fk_commented_post = p.id_post and c.status = 1
       ) as num_comments,
       (select sum(vp.type)
        from votes_posts vp
        where c.fk_voted_post = p.id_post
       ) as num_score       
from posts p
where p.status = 1;

```

The problem with `join` is that the counts get messed up because the two other tables are not related to each tother -- so you get a Cartesian product.
You want to join comments counts and votes counts to the posts. So, aggregate to get the counts, then join.

```
select
  p.*,
  coalesce(c.cnt, 0) as comments,
  coalesce(v.cnt, 0) as votes
from posts p
left join
(
  select fk_commented_post as id_post, count(*) as cnt
  from comments
  where status = 1
  group by fk_commented_post
) c on c.id_post = p.id_post
left join
(
  select fk_voted_post as id_post, count(*) as cnt
  from votes_posts
  group by fk_voted_post
) v on v.id_post = p.id_post
where p.status = 1
order by p.id_post;

```