I am looking for queries, using which I can analyze a general employee database. This is for Data Analysis.
Tried this for monthly employee trend
SELECT
dt.FullDateAlternateKey as 'Date'
, count(1) as ActiveCount
FROM DimDate dt
LEFT JOIN (SELECT 'Active' as 'EmpStatus', * FROM DimEmployee) emp
-- regular active employees
ON (dt.FullDateAlternateKey between emp.StartDate and ISNULL(emp.EndDate,'9999-12-31'))
WHERE
dt.FullDateAlternateKey = EOMONTH(dt.FullDateAlternateKey)
GROUP BY
dt.FullDateAlternateKey
ORDER BY
1;
also found CTE use for finding employee hierarchy
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ParentEmployeeKey, e.EmployeeKey, e.Title, e.DepartmentName,
0 AS Level
FROM DimEmployee AS e
WHERE e.ParentEmployeeKey IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ParentEmployeeKey, e.EmployeeKey, e.Title, e.DepartmentName,
Level + 1
FROM DimEmployee AS e
INNER JOIN DirectReports AS d
ON e.ParentEmployeeKey = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
WHERE DeptID = 'Information Services' OR Level = 0
also, some good queries to analyze the sales data
-- Show each sales average for Group, Country, and Region all in one query
SELECT DISTINCT
t.SalesTerritoryGroup
, t.SalesTerritoryCountry
, t.SalesTerritoryRegion
, AVG(s.SalesAmount) OVER(PARTITION BY t.SalesTerritoryGroup ) as 'GroupAvgSales'
, AVG(s.SalesAmount) OVER(PARTITION BY t.SalesTerritoryCountry ) as 'CountryAvgSales'
, AVG(s.SalesAmount) OVER(PARTITION BY t.SalesTerritoryRegion ) as 'RegionAvgSales'
FROM FactInternetSales s
JOIN DimSalesTerritory t ON
s.SalesTerritoryKey = t.SalesTerritoryKey
WHERE
YEAR(s.OrderDate) = 2013
ORDER BY
1,2,3
Use additional aggregations to understand more about product sales such as the distribution of sales etc..
SELECT
cat.EnglishProductCategoryName 'Category'
, sub.EnglishProductSubcategoryName 'SubCategory'
, count(1) 'Count' -- How many sales where there?
, sum(s.SalesAmount) 'Sales' -- How much sales did we have?
, avg(s.SalesAmount) 'Avg_SalesAmount' -- What was the Avg sale amount?
, min(s.SalesAmount) 'Min_SaleAmount' -- What was the Min sale amount?
, max(s.SalesAmount) 'Max_SaleAmount' -- What was the Max sale amount
FROM FactInternetSales s
LEFT JOIN DimProduct p ON s.ProductKey = p.ProductKey
LEFT JOIN DimProductSubcategory sub ON p.ProductSubcategoryKey = sub.ProductSubcategoryKey
LEFT JOIN DimProductCategory cat ON sub.ProductCategoryKey = cat.ProductCategoryKey
-- must use group by in order for aggregation to work properly
GROUP BY
cat.EnglishProductCategoryName -- column aliases aren't allowed
, sub.EnglishProductSubcategoryName
ORDER BY
cat.EnglishProductCategoryName
, sub.EnglishProductSubcategoryName
-- Calculate the customer acquisition funnel
SELECT
c.FirstName
, c.LastName
, c.DateFirstPurchase
, DATEDIFF(d,c.DateFirstPurchase,getdate()) as 'DaysSinceFirstPurchase' -- How long have they been a customer?
FROM DimCustomer c
ORDER BY 3 DESC
-- Calculate a Monthly average of customer tenure
SELECT
EOMONTH(c.DateFirstPurchase) as 'MonthOfFirstPurchase' -- What month did they become a customer?
, DATEDIFF(d,EOMONTH(c.DateFirstPurchase),getdate()) as 'DaysSinceFirstPurchase' -- How long have they been a customer?
, COUNT(1) as 'CustomerCount' -- How manY customers are there for this month?
FROM DimCustomer c
GROUP BY EOMONTH(c.DateFirstPurchase)
ORDER BY 2 DESC
-- Show the top product Sub Categories for each year
SELECT
count(DISTINCT s.SalesOrderNumber) 'OrderCount' -- use 1 instead of a field for faster performance
, RANK() OVER (PARTITION BY YEAR(s.OrderDate) ORDER BY sum(s.SalesAmount) DESC) 'SalesRank'
, sum(s.SalesAmount) 'TotalSales'
, cat.EnglishProductCategoryName 'Category'
, sub.EnglishProductSubcategoryName 'SubCategory'
, YEAR(s.OrderDate) 'Year'
FROM FactInternetSales s
INNER JOIN DimProduct p ON s.ProductKey = p.ProductKey
INNER JOIN DimProductSubcategory sub ON p.ProductSubcategoryKey = sub.ProductSubcategoryKey
INNER JOIN DimProductCategory cat ON sub.ProductCategoryKey = cat.ProductCategoryKey
-- must use group by in order for aggregation to work properly
GROUP BY
cat.EnglishProductCategoryName -- column aliases aren't allowed
, sub.EnglishProductSubcategoryName
, YEAR(s.OrderDate)
ORDER BY YEAR(s.OrderDate), SUM(s.SalesAmount) DESC;
-- first, create weekly sales totals
SELECT SUM(s.SalesAmount) 'WeeklySales'
, DATEPART(ww, s.OrderDate) as 'WeekNum'
FROM FactInternetSales s
WHERE YEAR(s.OrderDate) = 2013
GROUP BY
DATEPART(ww, s.OrderDate)
ORDER BY
DATEPART(ww, s.OrderDate) ASC
-- use that subquery as our source and calculate the moving average
SELECT
AVG(WeeklySales) OVER (ORDER BY WeekNum ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as AvgSales
, WeeklySales as 'TotalSales'
, WeekNum
FROM (
SELECT SUM(s.SalesAmount) 'WeeklySales'
, DATEPART(ww, s.OrderDate) as 'WeekNum'
FROM FactInternetSales s
WHERE YEAR(s.OrderDate) = 2013
GROUP BY
DATEPART(ww, s.OrderDate)
) AS s
GROUP BY
WeekNum, WeeklySales
ORDER BY
WeekNum ASC
-- Running Total
SELECT
SUM(MonthlySales) OVER (PARTITION BY SalesYear ORDER BY SalesMonth ROWS UNBOUNDED PRECEDING) as YTDSales
, MonthlySales as 'MonthlySales'
, SalesYear
, SalesMonth
FROM (
SELECT SUM(s.SalesAmount) 'MonthlySales'
, MONTH(s.OrderDate) as 'SalesMonth'
, year(s.OrderDate) as 'SalesYear'
FROM FactInternetSales s
GROUP BY
MONTH(s.OrderDate)
, year(s.OrderDate)
) AS s
GROUP BY
SalesMonth, SalesYear, MonthlySales
ORDER BY
SalesYear, SalesMonth ASC
-- Get Prev Year Sales
WITH MonthlySales (YearNum, MonthNum, Sales)
AS
(
SELECT d.CalendarYear, d.MonthNumberOfYear, SUM(s.SalesAmount)
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
GROUP BY d.CalendarYear, d.MonthNumberOfYear
)
-- Get Current Year and join to CTE for previous year
SELECT
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales PrevSales
, SUM(s.SalesAmount) CurrentSales
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
JOIN MonthlySales ms ON
d.CalendarYear-1 = ms.YearNum AND
d.MonthNumberOfYear = ms.MonthNum
GROUP BY
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales
ORDER BY
1 DESC, 2 DESC
-- Now calculate the % change Year over Year
WITH MonthlySales (YearNum, MonthNum, Sales)
AS
(
SELECT d.CalendarYear, d.MonthNumberOfYear, SUM(s.SalesAmount)
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
GROUP BY d.CalendarYear, d.MonthNumberOfYear
)
-- Get Current Year and join to CTE for previous year
SELECT
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales PrevSales
, SUM(s.SalesAmount) CurrentSales
, (SUM(s.SalesAmount) - ms.Sales) / SUM(s.SalesAmount) 'PctGrowth'
FROM DimDate d
JOIN FactInternetSales s ON d.DateKey = s.OrderDateKey
JOIN MonthlySales ms ON
d.CalendarYear-1 = ms.YearNum AND
d.MonthNumberOfYear = ms.MonthNum
GROUP BY
d.CalendarYear
, d.MonthNumberOfYear
, ms.Sales
ORDER BY
1 DESC, 2 DESC