Count how many first and last entries in given period of time are equal

Question

Given a table structured like that:

id   | news_id(fkey)|    status      |    date
1          10          PUBLISHED      2016-01-10
2          20          UNPUBLISHED    2016-01-10
3          10          UNPUBLISHED    2016-01-12
4          10          PUBLISHED      2016-01-15
5          10          UNPUBLISHED    2016-01-16
6          20          PUBLISHED      2016-01-18
7          10          PUBLISHED      2016-01-18
8          20          UNPUBLISHED    2016-01-20
9          30          PUBLISHED      2016-01-20
10         30          UNPUBLISHED    2016-01-21

I'd like to count distinct news that, in given period time, had first and last status equal(and also status equal to given in query)

So, for this table query from 2016-01-01 to 2016-02-01 would return:

• 1 (with WHERE status = 'PUBLISHED') because news_id 10 had PUBLISHED in both first( 2016-01-10 ) and last row (2016-01-18)
• 1 (with WHERE status = 'UNPUBLISHED' because news_id 20 had UNPUBLISHED in both first and last row

notice how news_id = 30 does not appear in results, as his first/last statuses were contrary.

I have done that using following query:

SELECT count(*) FROM
(
    SELECT DISTINCT ON (news_id)
    news_id, status as first_status
    FROM news_events
    where date >= '2015-11-12 15:01:56.195'
    ORDER BY news_id, date
) first
JOIN (
    SELECT DISTINCT ON (news_id)
    news_id, status as last_status
    FROM news_events
    where date >= '2015-11-12 15:01:56.195'
    ORDER BY news_id, date DESC

) last
using (news_id)
where first_status = last_status
and first_status = 'PUBLISHED'

Now, I have to transform query into SQL our internal Java framework, unfortunately it does not support subqueries, except when using EXISTS or NOT EXISTS. I was told to transform the query to one using EXISTS clause(if it is possible) or try finding another solution. I am, however, clueless. Could anyone help me do that?

edit: As I am being told right now, the problem lies not with our framework, but in Hibernate - if I understood correctly, "you cannot join an inner select in HQL" (?)

Answer 1

First of all, subqueries are a substantial part of SQL. A framework forbidding their use is a bad framework.

However, "first" and "last" can be expressed with NOT EXISTS: where not exists an earlier or later entry for the same news_id and date range.

select count(*)
from mytable first
join mytable last on last.news_id = first.news_id
where date between @from and @to
and not exists
(
  select *
  from mytable before_first
  where before_first.news_id = first.news_id
  and before_first.date < first.date
  and before_first.date >= @from
)
and not exists
(
  select *
  from mytable after_last
  where after_last.news_id = last.news_id
  and after_last.date > last.date
  and after_last.date <= @to
)
and first.status = @status
and last.status = @status;