Abaixo está uma instrução que descreve uma tarefa de geração de SQL, combinada com uma entrada que fornece mais contexto sobre os esquemas de tabela disponíveis. Escreva o código SQL que responda adequadamente à solicitação
 ###Instrução: Qual é o número de pessoal militar e os anos médios de serviço para cada ramo, particionados por patente e ordenados pelo número de pessoal militar em ordem decrescente?
 ###Tabelas: 
CREATE TABLE military_personnel (id INT, branch_id INT, rank_id INT, years_of_service INT, personnel_date DATE); INSERT INTO military_personnel (id, branch_id, rank_id, years_of_service, personnel_date) VALUES (1, 1, 1, 4, '2021-03-15'); INSERT INTO military_personnel (id, branch_id, rank_id, years_of_service, personnel_date) VALUES (2, 2, 2, 2, '2022-01-10'); CREATE TABLE branch (id INT, name VARCHAR(255)); INSERT INTO branch (id, name) VALUES (1, 'Army'); INSERT INTO branch (id, name) VALUES (2, 'Navy'); CREATE TABLE rank (id INT, name VARCHAR(255)); INSERT INTO rank (id, name) VALUES (1, 'Private'); INSERT INTO rank (id, name) VALUES (2, 'Petty Officer');
SELECT b.name as branch, r.name as rank, COUNT(mp.id) as num_military_personnel, AVG(mp.years_of_service) as avg_years_of_service, ROW_NUMBER() OVER (PARTITION BY b.name ORDER BY COUNT(mp.id) DESC) as rank FROM military_personnel mp JOIN branch b ON mp.branch_id = b.id JOIN rank r ON mp.rank_id = r.id GROUP BY b.name, r.name ORDER BY num_military_personnel DESC;