Festival - sql query to count new participants and participants that stopped coming

Question

I have tables for a festival that has been occurring for the 25 years. THhe tables are:

Member Table
| MemberID | Last_Name | First_Name |
|     1    |  Smith    |    John    |
|     2    | Johnson   |     Bob    |
|     3    |  Newman   |    Sam     |
|     4    |   White   |   John     |

Transactions Table
| TransactionID | MemberID | FestYear | 
|        1      |    1     |   2010   |
|        2      |    1     |   2011   |
|        3      |    1     |   2012   |
|        4      |    2     |   2010   |
|        5      |    3     |   2011   |
|        6      |    3     |   2012   |
|        7      |    4     |   2012   |

I want an output that counts (1) How many participants was this their first festival (they have never been to the festival previously). (2) How many participants was this their last festival (they never came again).

| FestYear | MemberFirstFestival | MemberLastFestival |
|   2010   |       2             |        1           |
|   2011   |       1             |        0           |
|   2012   |       1             |        3           |

I'm not that great at large SQL statements like this, so haven't got anywhere. I know I could write several looping sql statements, but that seems really inefficient.

Answer 1

We can do this with window functions and conditional aggregation:

select festYear, sum(rn1 = 1) MemberFirstFestival , sum(rn2 = 1) MemberLastFestival 
from (
    select t.*,
        row_number() over(partition by memberId order by festYear     ) rn1,
        row_number() over(partition by memberId order by festYear desc) rn2
    from transactions t
) t
group by festYear
order by festYear

festYear	MemberFirstFestival	MemberLastFestival
2010	2	1
2011	1	0
2012	1	3

fiddle