Análisis de Pérdida de clientes: Base de SQL

//// -- LEVEL 1
//// -- Tables and References
// Creating tables
 Table Sales {
    SalesOrderID int [pk, increment] 
    OrderDate date 
    DueDate date 
    ShipDate date 
    CustomerID int 
    SalesPersonID int
    TerritoryID int 
    BillToAddressID int 
    ShipToAddressID int 
    ShipMethodID int 
    CreditCardID int 
    SubTotal money 
    TaxAmt money 
    Freight money 
    TotalDue money
}
Table Stores {
    CustomerID int  [pk, increment] 
    Name VARCHAR(30) 
    AddressType VARCHAR(30)  
    AddressLine1 VARCHAR(60) 
    AddressLine2 VARCHAR(60) 
    City VARCHAR(30) 
    StateProvinceName VARCHAR(30) 
    PostalCode VARCHAR(15) 
    CountryRegionName VARCHAR 
}
Table Individuals{
    CustomerID int [pk, increment] 
    FullName nvarchar(152) 
    AddressType varchar(30) 
    AddressLine1 nvarchar(60) 
    AddressLine2 nvarchar(60) 
    City nvarchar(30) 
    StateProvinceName varchar(30) 
    PostalCode nvarchar(15) 
    CountryRegionName varchar(30) 
    EmailPromotion int 
    Occupation varchar(50) 
    Education varchar(50) 
    HomeOwnerFlag bit 
    NumberCarsOwned int 
    Gender char(1) 
    MaritalStatus char(1) 
    BirthDate date 
    DateFirstPurchase date 
}
TABLE Dates{
    DateId int 
    Date date  [pk]
    DateFullName varchar(15) 
    DayNumberOfMonth int 
    DayNumberOfQuarter int 
    DayNumberOfYear int 
    MonthKey smallint 
    MonthName varchar(10) 
    MonthFullKey int 
    MonthFullName varchar(15) 
    MonthNumberOfQuarter int 
    MonthNumberOfYear int 
    MonthDaysInMonth int 
    MonthBeginDate varchar(15) 
    MonthEndDate varchar(15) 
    QuarterKey smallint 
    QuarterName char(2) 
    QuarterFullKey int 
    QuarterFullName char(7) 
    QuarterNumberOfDays int 
    QuarterBeginDate varchar(15) 
    QuarterEndDate varchar(15) 
    YearKey smallint 
    YearName varchar(4) 
    YearNumberOfDays int 
    YearBeginDate varchar(15) 
    YearEndDate varchar(15) 
    DayKey smallint 
    DayName varchar(10) 
    WeekKey smallint 
    WeekName char(3) 
    WeekFullKey int 
    WeekFullName char(8) 
    WeekBeginDate varchar(15) 
    WeekEndDate varchar(15) 
    FiscalMonthFullName varchar(20) 
    FiscalQuarterFullName varchar(12) 
    FiscalYearKey smallint 
    FiscalYearName varchar(9) 
    FiscalYearBeginDate varchar(15) 
    FiscalYearEndDate varchar(15) 
    DateTypeKey smallint 
    DateTypeName varchar(12) 
    HolidayKey int 
    HolidayName varchar(40) 
    WeatherSeasonKey smallint 
    WeatherSeasonName varchar(20) 
    WeatherSeasonFullKey int 
    WeatherSeasonFullName varchar(20) 
    WeatherYearKey smallint 
    WeatherYearName varchar(4) 
    BusinessSeasonKey smallint 
    BusinessSeasonName varchar(20) 
    BusinessSeasonFullKey int 
    BusinessSeasonFullName varchar(26) 
    BusinessYearKey smallint 
    BusinessYearName varchar(4) 
}
Ref: Sales.CustomerID > Stores.CustomerID
Ref: Sales.CustomerID > Individuals.CustomerID
Ref: Sales.OrderDate > Dates.Date

SELECT SalesOrderID, 
       CAST(DATEADD(MONTH, 6, DATEADD(YEAR, 5, OrderDate)) AS DATE) AS OrderDate, 
       CAST(DATEADD(MONTH, 6, DATEADD(YEAR, 5, DueDate)) AS DATE) AS DueDate, 
       CAST(DATEADD(MONTH, 6, DATEADD(YEAR, 5, ShipDate)) AS DATE) AS ShipDate,
       ---, OnlineOrderFlag, SalesOrderNumber
       --, PurchaseOrderNumber, AccountNumber 
       CustomerID, 
       SalesPersonID, 
       TerritoryID, 
       --BillToAddressID, 
       --ShipToAddressID, 
       --ShipMethodID, 
       --CreditCardID,
       --, CreditCardApprovalCode
       --, CurrencyRateID 
       SubTotal, 
       TaxAmt, 
       Freight, 
       TotalDue
FROM Sales.SalesOrderHeader AS OH;

CREATE VIEW Demos.Stores AS 
    WITH BaseStore AS (
        SELECT 
            Cus.CustomerID
            ,s.Name 
            ,at.Name AS AddressType
            ,a.AddressLine1 
            ,a.AddressLine2 
            ,a.City 
            ,sp.Name AS StateProvinceName 
            ,a.PostalCode 
            ,cr.Name AS CountryRegionName 
            , ROW_NUMBER() OVER(PARTITION BY Cus.CustomerID
                ORDER BY CASE at.Name WHEN 'Main Office'  THEN 1    
                                      WHEN 'Primary'  THEN 2      
                                      WHEN 'Billing'  THEN 3    
                                      ELSE 4 END, bea.AddressTypeID ) AS RSel
        FROM Sales.Customer AS Cus
        JOIN Sales.Store AS S
            ON Cus.StoreID=S.BusinessEntityID
        --- Dups
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        INNER JOIN Person.CountryRegion AS cr 
            ON cr.CountryRegionCode = sp.CountryRegionCode
        INNER JOIN Person.AddressType AS at 
            ON at.AddressTypeID = bea.AddressTypeID
        WHERE Cus.StoreID IS NOT NULL
        AND Cus.CustomerID IN (SELECT  oh.CustomerID
            FROM Sales.SalesOrderHeader AS OH)
    )
    SELECT CustomerID, Name, AddressType, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode
        , CountryRegionName 
    FROM BaseStore
    WHERE RSel=1;
SELECT TOP 20 * 
FROM Demos.Stores;

CREATE VIEW Demos.Individuals
AS 
    WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS sv)
    , BaseIndividuos AS (
    SELECT 
        C.CustomerID,
        RTRIM(
            CONCAT(
                COALESCE(p.FirstName + ' ', '')
                , COALESCE(p.MiddleName + ' ', '')
                , COALESCE(p.LastName, '')
            )) AS FullName
        ,at.Name AS AddressType
        ,a.AddressLine1
        ,a.AddressLine2
        ,a.City
        ,StateProvinceName = sp.Name
        ,a.PostalCode
        ,CountryRegionName = cr.Name
        ,p.EmailPromotion
        , C.value('sv:Occupation[1]','varchar(50)') AS Occupation
        , C.value('sv:Education[1]','varchar(50)') AS Education
        , C.value('sv:HomeOwnerFlag[1]','bit') AS HomeOwnerFlag
        , C.value('sv:NumberCarsOwned[1]','int') AS NumberCarsOwned
        , C.value('sv:Gender[1]','char(1)') AS Gender
        , C.value('sv:MaritalStatus[1]','char(1)') AS MaritalStatus
        , C.value('sv:BirthDate[1]','date') AS BirthDate
        , CAST(DATEADD(MONTH, 6, DATEADD(YEAR, 5, 
            C.value('sv:DateFirstPurchase[1]','date'))) AS DATE) AS DateFirstPurchase
        , ROW_NUMBER() OVER(PARTITION BY C.CustomerID
            ORDER BY CASE at.Name WHEN 'Home'  THEN 1   
                                    ELSE 4 END, bea.AddressTypeID ) AS RSel
    FROM Sales.Customer AS c
    JOIN Person.Person p
        ON c.PersonID = p.BusinessEntityID
        INNER JOIN Person.BusinessEntityAddress bea 
        ON bea.BusinessEntityID = p.BusinessEntityID 
        INNER JOIN Person.Address a 
        ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince sp 
        ON sp.StateProvinceID = a.StateProvinceID
        INNER JOIN Person.CountryRegion cr 
        ON cr.CountryRegionCode = sp.CountryRegionCode
        INNER JOIN Person.AddressType at 
        ON at.AddressTypeID = bea.AddressTypeID
        LEFT OUTER JOIN Person.EmailAddress ea
        ON ea.BusinessEntityID = p.BusinessEntityID
        LEFT OUTER JOIN Person.PersonPhone pp
        ON pp.BusinessEntityID = p.BusinessEntityID
        LEFT OUTER JOIN Person.PhoneNumberType pnt
        ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
    CROSS APPLY p.Demographics.nodes('/sv:IndividualSurvey') AS SV(C)
    WHERE c.PersonID IS NOT NULL
        AND c.CustomerID IN (SELECT  oh.CustomerID
            FROM Sales.SalesOrderHeader AS OH)
    )
    SELECT CustomerID, FullName, AddressType, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode
        , CountryRegionName, EmailPromotion, Occupation, Education, HomeOwnerFlag, NumberCarsOwned, Gender
        , MaritalStatus, BirthDate, DateFirstPurchase
    FROM BaseIndividuos
    WHERE RSel=1;