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

Not sure if this adresses you problem correctly, since it is more of a workaround. But considering the following:

News need to be published before they can be "unpublished". So if you'd add 1 for each "published" and substract 1 for each "unpublished" your balance will be positive (or 1 to be exact) if first and last is "published". It will be 0 if you have as many unpublished as published and negative, if it has more unpublished than published (which logically cannot be the case but obviously might arise, since you set a date threshhold in the query where a 'published' might be occured before).

You might use this query to find out:

SELECT SUM(CASE status WHEN 'PUBLISHED' THEN 1 ELSE -1 END) AS 'publishbalance'
FROM news_events
WHERE date >= '2015-11-12 15:01:56.195'
GROUP BY news_id