Sub-volume and average in Mysql

Question

Given 3 tables of a database that simulates a sense of Brazilian municipalities:

CREATE TABLE municipio (cod_mun char (7), nome varchar(50), cod_uf(2), CONSTRAINT municipio_pk PRIMARY KEY (cod_mun), CONSTRAINT cod_uf_fk FOREIGN KEY (cod_uf) REFERENCES uf (cod_uf));

CREATE TABLE senso (ano int(4), cod_mun char(7), pib decimal(12,3), populacao (11), CONSTRAINT senso_pk PRIMARY KEY (ano, cod_mun), CONSTRAINT cod_mun_fk FOREIGN KEY (cod_mun) REFERENCES municipio (cod_mun));```

Tem-se a seguinte pergunta:
Quais são as cidades do estado da Bahia cuja população é maior que a média do estado em cada ano?

Eu consegui selecionar a média do estado da Bahia por ano com a seguinte query:

```SELECT AVG(populacao), ano FROM senso WHERE cod_mun IN (SELECT cod_mun FROM municipio WHERE cod_uf IN (SELECT cod_uf FROM uf WHERE estado = 'Bahia')) GROUP BY ano;```

Entretanto, a partir daqui não sei como faço para selecionar as cidades bahianas que possuem populações maiores do que a média do estado nos respectivos anos de senso.

Answer 1

SELECT m.name, s.year FROM municipio as m LEFT JOIN senso as s.cod_mun = m.cod_mun WHERE s.populacao > (SELECT AVG(populacao) FROM senso WHERE ano = s.ano) as media_populacao GROUP BY s.ano, m.nome ORDER BY s.ano DESC, m.nome ASC