How can I write this SQL query?

Question

I was wondering if I could get some help on this sql query. It´s from a test which I failed and now wanted to learn. I've spent a lot of time on it, watched tutorials on sql but I still don't get how it could be written..

There are 2 tables (flights, occupation_flights) with flight information and the task is to: Write a query that gives a list of flights for the next 7 days, and shows the flight, the number of available seats, number of occupied seats and a % of occupied seats.

FLIGHTS:                    
FLIGHT  ID_COMPANY   DATE      ORIGIN   DESTINATION PLACES
1003       D3      20/01/2006   MADRID  LONDRES       40
1007       AF      20/01/2006   PARIS   MALAGA        12
1003       15      30/01/2006   MADRID  LONDRES       20
1007       AF      30/01/2006   PARIS   MALAGA        17

(PLACES show number of places offered on each flight)

OCCUPATION_FLIGHTS:
FLIGHT ID_COMPANY   DATE    PASSENGER   SEAT
1003    IB       20/01/2006 07345128H   01V
1003    IB       20/01/2006 1013456213  01P
1003    IB       20/01/2006 08124356C   02V
1003    IB       20/01/2006 57176355K   02P
1007    AF       20/01/2006 27365138A   
1003    IB       30/01/2006 734512811   01V
1003    IB       30/01/2006 1013456213  02V
1003    IB       30/01/2006 57176355K   

(When SEAT is empty, that means a person has not yet issued the ticket). TEST DATA I thought to get a number of available seats I should sum up cases when passenger is not empty per flight and date and extract that from PLACES. So I started with smth like:

SELECT 
f.flight, 
f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END)      
FROM occupation_flights AS of GROUP BY of.flight, of.date) AS available,
f.places - (SELECT (CASE WHEN of.passanger IS NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date) AS occupied,
100.0 * ((f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date)) / f.places AS %occupied
FROM flights AS f
JOIN occupation_flights AS of ON f.flight=of.flight
WHERE f.date BETWEEN DateAdd(DD,-7,GETDATE() ) and GETDATE() 
GROUP BY f.flight

but it's not finished, because I really don't understand how to make him calculate it in groups by flight and date at the same time, because there are flights with the same flight number but going on different dates. I also don't have access to tables, the test was on paper and to show that you know how to write sql queries. Would be really grateful for any insights! Many thanks in advance!

Answer 1

Mysql conform: SQLFIDDLE

SELECT f.flight,f.mydate,f.places - count(of.seat) as available_seats,
        count(of.seat) as occupied,
        count(of.seat)/f.places * 100 as percentage_occupied
FROM flights f
JOIN occupation_flights of
ON f.flight = of.flight AND f.mydate = of.mydate
WHERE of.mydate BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 DAY)AND CURDATE() 
group by f.flight, f.mydate