SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Booking ( order_id int, order_date date, line_of_business varchar(255), fare decimal(10,2) ); INSERT INTO Booking VALUES (1, '2019-01-01','Hotels',375.14) ,(2, '2019-02-02','Flights',280) ,(3, '2019-03-03','Car',51.22) ,(4, '2020-04-01','Hotels',400) ,(5, '2020-05-02','Flights',250) ,(6, '2020-06-03','Car',51.22); SELECT order_year ,line_of_business ,orders ,orders / NULLIF( LAG(orders) OVER (PARTITION BY order_year, line_of_business ORDER BY order_year) , 0) - 1 as order_pct_growth ,total_fare / NULLIF( LAG(total_fare) OVER (PARTITION BY order_year, line_of_business ORDER BY order_year) , 0) - 1 as fare_pct_growth FROM ( SELECT YEAR(order_date) as order_year ,line_of_business ,count(*) as orders ,sum(fare) as total_fare FROM booking GROUP BY YEAR(order_date) ,line_of_business )