Nobel Prizes Dataset

This is a JSON dataset of Nobel Prizes from https://api.nobelprize.org/v1/prize.json

Convert to SQL

Used JSON to SQL Converter to generate SQL

Run the generated DDL and Insert statements here


Examine the dataset


All nobel laureates from the year 1977

select * from nobel_prizes p inner join 
    nobel_prizes_laureates l on p.nobel_prizes_pk=l.nobel_prizes_fk where 
p.year='1977' ;

Years with most shared prizes

select year, sum(shared_by) as total_shared_by from 
nobel_prizes p
    inner join 
(select nobel_prizes_fk, count(share::int) as shared_by
    from nobel_prizes_laureates l group by nobel_prizes_fk) l
on p.nobel_prizes_pk=l.nobel_prizes_fk and shared_by > 1
group by year order by total_shared_by desc;

Challenge

  • Find the category with most shared prizes
SQL Output