transactionsTest.sql

USE master
GO

CREATE DATABASE transactions
GO

ALTER DATABASE transactions SET RECOVERY SIMPLE
GO

USE transactions
GO


IF OBJECT_ID('dbo.someTransactions') IS NOT NULL
	DROP TABLE dbo.someTransactions
GO

CREATE TABLE dbo.someTransactions (

	Id						INT IDENTITY PRIMARY KEY,
	ProductId				INT NOT NULL,
	ProductName				VARCHAR(40) NOT NULL,
	City					VARCHAR(30) NOT NULL,
	[State]					VARCHAR(30) NOT NULL,
	Country					VARCHAR(30) NOT NULL,
	UnitCost				MONEY NOT NULL,
	SellAmount				MONEY NOT NULL,
	NumberOfTimesPurchased	INT NOT NULL,

	Profit AS ( NumberOfTimesPurchased * ( SellAmount - UnitCost ) ) 

	)
GO


;WITH cte AS
(
SELECT TOP 20 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
	CROSS JOIN master.sys.columns c2
	CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.someTransactions ( ProductId, ProductName, City, [State], Country, UnitCost, SellAmount, NumberOfTimesPurchased )
SELECT
	p.ProductKey AS ProductId,
	p.EnglishProductName AS ProductName,
	g.City  AS City,
	g.StateProvinceName AS State,
	g.EnglishCountryRegionName AS Country,
	f.UnitPrice AS UnitCost,
	f.SalesAmount + ( f.SalesAmount * 0.2 ) AS SellAmount,
	OrderQuantity AS NumberOfTimesPurchased

FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] f
	INNER JOIN [AdventureWorksDW2012].[dbo].[DimProduct] p ON f.ProductKey = p.ProductKey
	INNER JOIN [AdventureWorksDW2012].[dbo].[DimCustomer] c ON f.CustomerKey = c.CustomerKey
		INNER JOIN [AdventureWorksDW2012].[dbo].[DimGeography] g ON c.GeographyKey = g.GeographyKey
	CROSS JOIN cte x
GO
:exit






-- Sample queries
SELECT 
    City, AVG(UnitCost), AVG(SellAmount), 
    SUM(NumberOfTimesPurchased), AVG(Profit) 
FROM 
    dbo.someTransactions
GROUP BY  
    City

SELECT 
    State, AVG(UnitCost), AVG(SellAmount), SUM(NumberOfTimesPurchased), 
    AVG(Profit) 
FROM 
    dbo.someTransactions
GROUP BY 
    State

SELECT 
    Country, AVG(UnitCost), AVG(SellAmount), SUM(NumberOfTimesPurchased), 
    AVG(Profit) 
FROM 
    dbo.someTransactions
GROUP BY 
    Country

SELECT 
    ProductId, ProductName, AVG(UnitCost), AVG(SellAmount), 
    SUM(NumberOfTimesPurchased), AVG(Profit) 
FROM 
    dbo.someTransactions
GROUP BY 
    ProductId, ProductName


--These queries are quick: ~1 second. However, I've noticed that under load, performance significantly drops (from 1 seco