Among the employees who are married and wish to receive e-mail promotions, how much higher is their highest pay rate from the average pay rate?
married refers to MaritalStatus = 'M'; Contact does wish to receive e-mail promotions from Adventure Works refers to EmailPromotion = 1; Average = Divide (Sum(Rate (MaritalStatus = 'M' & EmailPromotion = 1))), Count (BusinessEntityID (MaritalStatus = 'M' & EmailPromotion = 1)); MAX(Rate (MaritalStatus = 'M' & EmailPromotion = 1) - Average;
SELECT MAX(T1.Rate) - SUM(T1.Rate) / COUNT(T1.BusinessEntityID) FROM EmployeePayHistory AS T1 INNER JOIN Person AS T2 ON T1.BusinessEntityID = T2.BusinessEntityID INNER JOIN Employee AS T3 ON T2.BusinessEntityID = T3.BusinessEntityID WHERE T2.EmailPromotion = 2 AND T3.MaritalStatus = 'M'