Foros Python

Hola.

Tengo este ejercicio que no me entero de nada y tengo que entregarlo hoy pero se me hace muy muy grande. ¿Alguien podría ayudarme a resolverlo?

Tengo que decir: a) Number of dishes for each restaurant. b) restaurants that didn't sell anything last month. c) numer of burguers (dish.name = "burguer") sold in 2021 per restaurant. d) month with the highest revenue in 2021. e) date of the last order per restaurant.

Muchas gracias de antemano y disculpad las molestias.

[attachment=140]

Hola. Por mi parte, SQL no es mi especialidad así que no puedo serte de mucha ayuda. Tal vez deberías recurrir a un foro de SQL.

Saludos

El dialector usado es SQLite, pero debe darte una idea

Código:
-- a

select count(*) as number_of_dishes, r.name as restaurant_name

from dish d inner join restaurant r on r.id = d.restaurant_id 

group by d.restaurant_id 

-- b

select r.name as restaurant_name from restaurant r 

where r.id not in (

select d.restaurant_id 

from "order" o 

inner join order_dish od on o.id = od.order_id 

inner join dish d on d.id = od.dish_id 

where o.created_on BETWEEN datetime('now', 'start of month','-1 month') AND datetime('now', 'localtime'));

-- c

select sum(od.quantity) as number_of_burges, r.name as restaurant_name

from dish d inner join order_dish od on od.dish_id = d.id 

inner join restaurant r on r.id = d.restaurant_id 

inner join "order" o on o.id = od.order_id  

where d.name = 'Burguer' and strftime('%Y',o.created_on) = '2021'

group by d.restaurant_id 

-- d

select r.name as restaurant, max(o.created_on) as last_order_date  

from "order" o 

inner join order_dish od on od.order_id = o.id 

inner join dish d on d.id = od.dish_id 

INNER join restaurant r on r.id = d.restaurant_id 

group by r.name

order by o.created_on DESC

El dialector usado es SQLite, pero debe darte una idea

novatoPython

Francisco

Costero