59 | | --sql here |
| 59 | select q2.god as godina, q2.maximum as max, |
| 60 | case |
| 61 | when q3.species = 0 then 'cat' |
| 62 | when q3.species = 1 then 'dog' |
| 63 | when q3.species = 2 then 'bird' |
| 64 | end as species |
| 65 | |
| 66 | from |
| 67 | ( select q1.godina as god, max(broj_posvojuvanja) as maximum from |
| 68 | ( |
| 69 | --posvojuvanja po godini i tipovi na milenichinja |
| 70 | select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja |
| 71 | from adoption a |
| 72 | join pet p2 on a.id_adoption = p2.id_adoption |
| 73 | where (end_date_foster is null) and (a.approved is true) |
| 74 | group by godina, p2.species |
| 75 | --order by godina |
| 76 | ) q1 |
| 77 | group by godina |
| 78 | )q2 |
| 79 | join |
| 80 | ( |
| 81 | select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja |
| 82 | from adoption a |
| 83 | join pet p2 on a.id_adoption = p2.id_adoption |
| 84 | where (end_date_foster is null) and (a.approved is true) |
| 85 | group by godina, p2.species |
| 86 | --order by godina |
| 87 | ) q3 |
| 88 | on q2.god = q3.godina and q2.maximum = q3.broj_posvojuvanja |