Trying To Write A Query That Displays The Names Of All Authors That Published Papers In Two Consecutive Years

The database that maintains author and publication information has the following schema: CREATE TABLE Author (aid integer NOT NULL, name varchar(50) NOT NULL, affiliation varchar(5

Solution 1:

I would use lag(). To get the author ids:

select p.aid
from (select a.aid, p.year,
             lag(p.year) over (partition by a.aid orderby p.year) as prev_year
      from papers p join
           authored a
           on a.pid = p.pid
      groupby a.aid, p.year
     ) p
where prev_year = year - 1

You can then use in or join or whatever to get the full author information:

select a.*
from authors a
where a.aid in (select a.aid
                from (select a.aid, p.year, 
                             lag(p.year) over (partition by a.aid order by p.year) as prev_year
                      from papers p join
                           authored a
                           on p.pid = a.pid
                      groupby a.aid, p.year
                     ) p
                where prev_year = year - 1
               );

You actually don't need lag, but it is likely to be much more efficient. An alternative is:

with pa as (select p.*, a.aid
      from papers p join
           authors a
           on p.pid = p.pid
     )
select a.*
from authors a
where a.aid in (select p.aid
                from pa p join
                     pa p_prev
                     on p_prev.aid = p.aid and
                        p_prev.year = p.year - 1);

Solution 2:

Try the following, it seems like gaps and island problem. Here is the small example demo, which will give you an idea how to solve your problem.

select
    id,
    name
from
(
    select
        aid,
        name,
        count(*) over (partitionby rnk) as total
    from
    (
        select 
            aid as id, 
            name,
            year-row_number() over (partitionby aid, name orderbyyear) as rnk
        from authored au            

        innerjoin author a
        on au.aid = a.aid

        innerjoin paper p
        on au.pid = p.pid
    ) val
) fin
where total =2

Solution 3:

It doesn't seem that complicated

SELECT au1.aid as id, au1.name AS name
FROM author au1 INNER join authored authored1 ON au1.aid=authored1.aid
INNER join paper p1 ON authored1.pid=p1.pid
WHERE au1.aid IN (SELECT au2.aid FROM author au2 
                  INNER join authored authored2 ON au1.aid=authored2.aid
                  INNER join paper p2 ON au2.pid=p1.pid AND  p2.year = p1.year+1)
ORDERby au1.aid;

You can also use SQL EXISTS instead of IN