select a. * ,
case when a.nominee_index<5 then 1
when a.nominee_index<9 then 2
when a.nominee_index<13 then 3
end as row_index
,case when a.nominee_index IN (1,5,9) then 1
when a.nominee_index IN (2,6,10) then 2
when a.nominee_index IN (3,7,11) then 3
when a.nominee_index IN (4,8,12) then 4
end as column_index
,'https://raw.github.com/tashapiro/supreme-court-justices/main/images/' || UPPER(nominee) || '.png' as image
from
(select s. * ,
case when s.nominee = 'Souter' then 1
when s.nominee='Thomas' then 2
when s.nominee='Ginsburg' then 3
when s.nominee='Breyer' then 4
when s.nominee='Roberts' then 5
when s.nominee='Alito' then 6
when s.nominee='Kagan' then 7
when s.nominee='Sotomayor' then 8
when s.nominee='Gorsuch' then 9
when s.nominee='Kavanaugh' then 10
when s.nominee='Barrett' then 11
when s.nominee = 'Jackson' then 12 end as nominee_index
from scotus_votes s
) a