Help me in solving SQW46 problem

help
,
1

My issue

/* Write a query to do the following

  • We need to output the following - ‘lane_id’, ‘origin’, ‘destination’, and compute ‘profit’
  • Order by decreasing ‘profitability’
  • ‘profit’ is the alias for the column which stores the values ‘Revenue’ - ‘running_cost’
  • ‘running_cost’ is computed as (cost_per_distance x distance)
  • Hint: you will need to use join all the tables - ‘Revenue’, ‘Truck’ and ‘Network’ and apply relevant conditions
  • Hint: This has to be computed only for the truck_type ‘medium’ */
    SELECT
    N.connection_id as lane_id,
    N.origin,
    N.destination,
    (R.revenue - (T.cost_per_distance * N.distance)) AS profit
    FROM
    Network N
    JOIN
    Truck T ON N.truck_id = T.truck_id
    JOIN
    Revenue R ON N.connection_id = R.lane_id
    WHERE
    T.truck_id = ‘Truck2’
    ORDER BY
    profit DESC;

My code

/* Write a query to do the following
- We need to output the following - 'lane_id', 'origin', 'destination', and compute 'profit' 
- Order by decreasing 'profitability' 
- 'profit' is the alias for the column which stores the values 'Revenue' - 'running_cost'
- 'running_cost' is computed as (cost_per_distance x distance)
- **Hint:** you will need to use join all the tables - 'Revenue', 'Truck' and 'Network' and apply relevant conditions
- **Hint:** This has to be computed only for the truck_type 'medium' */
SELECT
    N.connection_id as lane_id,
    N.origin,
    N.destination,
    (R.revenue - (T.cost_per_distance * N.distance)) AS profit
FROM
    Network N
JOIN
    Truck T ON N.truck_id = T.truck_id
JOIN
    Revenue R ON N.connection_id = R.lane_id
WHERE
    T.truck_id = 'Truck2'
ORDER BY
    profit DESC;

Learning course: Learn Data Analytics using SQL and Python
Problem Link: CodeChef: Practical coding for everyone

2

SELECT
r.lane_id,
r.origin,
r.destination,
(r.revenue - (t.cost_per_distance * n.distance)) AS profit
FROM
Revenue r
JOIN
Network n ON r.origin = n.origin AND r.destination = n.destination
JOIN
Truck t ON n.truck_id = t.truck_id
WHERE
t.truck_id = ‘Truck2’
ORDER BY
profit DESC;