New Products

You are given a table of product launches by company by year. Write a query to count the net difference between the number of products companies launched in 2020 with the number of products companies launched in the previous year. Output the name of the companies and a net difference of net products released for 2020 compared to the previous year.

table name: car_launches

Solution Explanation:

  • group by company_name to get sum values of all products of 2020 and 2019
  • We are going to use case expression to filter the values of year 2020 and 2019.
  • Apply sum function to get the addition of all products of particular years and then subtrat them to get difference.
sum(case when year=2020 then 1 else 0 end)
sum(case when year=2019 then 1 else 0 end)

Solution:

select (sum(case when year=2020 then 1 else 0 end)-
sum(case when year=2019 then 1 else 0 end)) as absDiff
,company_name from car_launches
group by company_name;

Output:

SQL Script:

USE [StrataScratch]
CREATE TABLE [dbo].[car_launches](
	[year] [int] NULL,
	[company_name] [varchar](50) NULL,
	[product_name] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Toyota', N'Avalon')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Toyota', N'Camry')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Toyota', N'Corolla')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Honda', N'Accord')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Honda', N'Passport')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Honda', N'CR-V')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Honda', N'Pilot')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Honda', N'Civic')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Chevrolet', N'Trailblazer')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Chevrolet', N'Trax')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Chevrolet', N'Traverse')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Chevrolet', N'Blazer')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Ford', N'Figo')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Ford', N'Aspire')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Ford', N'Endeavour')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Jeep', N'Wrangler')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Jeep', N'Cherokee')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2020, N'Jeep', N'Compass')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Jeep', N'Renegade')
GO
INSERT [dbo].[car_launches] ([year], [company_name], [product_name]) VALUES (2019, N'Jeep', N'Gladiator')
GO


Comments (0)
Questions List