Festival - sql查询统计新加入的参与者和退出的参与者

Question

我有一个已经举办了25年的节日的桌子。这些表是:

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   |

我想要一个计数的输出(1)有多少参与者是他们第一次参加这个节日(他们以前从未参加过这个节日)。(2)这是他们最后一次参加音乐节吗?(他们再也没有来过)

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

我不擅长像这样的大型SQL语句，所以没有任何进展。我知道我可以写几个循环sql语句，但这似乎真的很低效。

Answer 1

我们可以通过窗口函数和条件聚合来做到这一点:

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

MemberLastFestival03