Need help in querying the below logic:
I have to calculate renewals. An org can order up to 5 licenses in total from us
License Table 2018
| Year | Licenses |
|---|---|
| 2018 | A |
| 2018 | A |
| 2018 | B |
| 2018 | B |
| 2018 | C |
| 2019 | A |
| 2019 | A |
| 2019 | B |
License Table 2019:
| Year | Licenses |
|---|---|
| 2019 | A |
| 2019 | A |
| 2019 | B |
Result:
| License | Renewal Percentage |
|---|---|
| A | 100% |
| B | 50% |
| C | 0% |
More conditions:
Suppose the 2 order dates are 6/30/2019 and 7/1/2020, which are in FY19 and FY21 respectively. Even though the 2nd order is barely more than 1 year later, it is not treated as a renewal.