Advanced SQL: Using 1=1 JOIN to Compare Top Sellers & Company's Avg Sales per Transaction

Ever used 1=1 Join? Discover the power of the 1=1 JOIN technique in this SQL video! In this tutorial, we'll compare individual top sellers (salesperson) against the company's average sales. By the end, you'll understand how to effectively analyze sales per transaction per salesperson and identify standout performances. Below is the script to create and insert tables and records to do a practical exercise with me while watching the video.
------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Salesperson (
SalespersonID INT PRIMARY KEY,
Name VARCHAR(255)
);
------------------------------------------------------------------------------------------------------------------------
INSERT INTO Salesperson (SalespersonID, Name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Jake'),
(4, 'Jill'),
(5, 'Jim');
------------------------------------------------------------------------------------------------------------------------
CREATE TABLE ProductsSold (
TransactionID INT PRIMARY KEY,
ProductID INT,
SalesAmount DECIMAL(10,2),
SalespersonID INT,
FOREIGN KEY (SalespersonID) REFERENCES Salesperson(SalespersonID)
);
------------------------------------------------------------------------------------------------------------------------
INSERT INTO ProductsSold (TransactionID, ProductID, SalesAmount, SalespersonID) VALUES
(1, 101, 100.50, 1),
(2, 102, 150.30, 1),
(3, 103, 80.20, 2),
(4, 104, 90.80, 2),
(5, 105, 120.25, 3),
(6, 101, 85.50, 3),
(7, 102, 95.00, 4),
(8, 103, 110.20, 4),
(9, 104, 105.30, 5),
(10, 105, 140.75, 5),
(11, 101, 110.00, 1),
(12, 102, 125.50, 2),
(13, 103, 95.30, 2),
(14, 104, 90.50, 3),
(15, 105, 100.25, 3),
(16, 101, 115.50, 4),
(17, 102, 120.00, 4),
(18, 103, 90.20, 5),
(19, 104, 100.10, 5),
(20, 105, 130.55, 1),
(21, 101, 125.50, 2),
(22, 102, 135.60, 3),
(23, 103, 105.70, 4),
(24, 104, 115.80, 5),
(25, 105, 100.90, 1),
(26, 101, 110.50, 2),
(27, 102, 120.60, 3),
(28, 103, 130.70, 4),
(29, 104, 140.80, 5),
(30, 105, 150.90, 1),
(31, 101, 95.50, 2),
(32, 102, 105.60, 3),
(33, 103, 115.70, 4),
(34, 104, 125.80, 5),
(35, 105, 135.90, 1),
(36, 101, 145.00, 2),
(37, 102, 155.10, 3),
(38, 103, 165.20, 4),
(39, 104, 175.30, 5),
(40, 105, 185.40, 1);
-----------------------------------------------------------------------------------------------------------------------
#Sqlcoding #windowfunction #CTE #commontableexpression #WITHCTE #Having #DateBetween #sqlServer #businessAnalyst #FAANGSQL #AppleInterview #AppleInterviewQuestion #cumulativeSales
#SQLCUmulative #SQLRANk #DenseRank #SubQuery #QueryOptimization #SqlOptimization
#SQLTop5 #SQLHighest #top3Employees #AvgDepartmentSalary #subqueries #SQL #Database #SQLJoin #SQLTutorial #DataAnalysis #SalesAnalysis #SQLQuery #ProductsSold, #Salesperson #DatabaseDesign, #SQLTips #AdvancedSQL #DataModeling #SalesTracking #1=1JOIN #1equal1SQLJOIN #Subquery
-----------------------------------------------------------------------------------------------------------------------

[@senp4i186]

I went from very basic knowledge of joins to quite advanced in this vid