query.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

-- query every anime with recent score
select
	title_pref, score
from anime
join stats on stats.id =
	(
	select
		id
	from stats
	where
		anime.mal_id = stats.mal_id
	order by created_at desc limit 1
	)
order by title_pref, score desc;

-- query historic stats for one anime
select 
	title_pref,
	anime_type, 
	score, 
	popularity, 
	members, 
	(watching+completed+onhold+dropped) as members_watching, 
	plan_to_watch, 
	favorites, 
	season_name, 
	season_year, 
	stats.created_at
from anime 
join stats on 
	anime.mal_id = stats.mal_id 
where 
	anime.mal_id = 38883 
order by stats.created_at;