Performed advanced date and time manipulations, aggregations, and time series analyses as a server developer.

HorizonFitness

-- Created date tables and relationships between the data for the start NGO
SELECT 
    cv.CustomerID,
    cv.CustomerVisitStart,
    cv.AmenityUseInMinutes,
    c.Date,
    c.CalendarYear,
    c.MonthName,
    c.DayOfWeek
FROM 
    CustomerVisits cv
JOIN 
    CalendarTable c ON CAST(cv.CustomerVisitStart AS DATE) = c.Date; GO

SELECT 
    i.IncidentDate,
    i.IncidentType,
    i.NumberOfIncidents,
    c.CalendarYear,
    c.CalendarQuarter,
    c.MonthName
FROM 
    Incidents i
JOIN 
    CalendarTable c ON i.IncidentDate = c.Date; GO

SELECT 
    mv.CustomerID,
    mv.MonthStartDate,
    mv.NumberOfVisits,
    mv.TotalTimeSpentInHours,
    c.CalendarYear,
    c.MonthName
FROM 
    MonthlyVisits mv
JOIN 
    CalendarTable c ON mv.MonthStartDate = c.Date; GO

SELECT 
    cv.CustomerID,
    cv.CustomerVisitStart,
    cv.AmenityUseInMinutes,
    mv.NumberOfVisits,
    mv.TotalTimeSpentInHours
FROM 
    CustomerVisits cv
JOIN 
    MonthlyVisits mv ON cv.CustomerID = mv.CustomerID 
    AND DATEADD(MONTH, DATEDIFF(MONTH, 0, cv.CustomerVisitStart), 0) = mv.MonthStartDate; GO

SELECT 
    ed.EventDate,
    ed.TimeZone,
    c.Date,
    c.Year,
    c.MonthName
FROM 
    EventDates ed
JOIN 
    CalendarTable c ON TRY_CONVERT(DATE, ed.EventDate) = c.Date
WHERE 
    ed.IsValidDate = 'True'; GO
    
-- Also created several views to be use for visualisation on PowerBI
--Focus group customer visits with calendar view
CREATE VIEW CustomerVisitsWithCalendar AS
SELECT 
    cv.CustomerID,
    cv.CustomerVisitStart,
    cv.AmenityUseInMinutes,
    c.Date,
    c.CalendarYear,
    c.MonthName,
    c.DayOfWeek
FROM 
    CustomerVisits cv
JOIN 
    CalendarTable c ON CAST(cv.CustomerVisitStart AS DATE) = c.Date; GO

-- A view that summarizes customer visits by month, showing total visits and time spent.
CREATE VIEW MonthlyVisitSummary AS
SELECT 
    CustomerID,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CustomerVisitStart), 0) AS MonthStartDate,
    COUNT(*) AS NumberOfVisits,
    SUM(AmenityUseInMinutes) / 60.0 AS TotalTimeSpentInHours,
    AVG(AmenityUseInMinutes) AS AvgTimePerVisit
FROM 
    CustomerVisits
GROUP BY 
    CustomerID, DATEADD(MONTH, DATEDIFF(MONTH, 0, CustomerVisitStart), 0); GO

-- Created a focus event dates view that filters and converts event dates into a usable format for analysis.
CREATE VIEW ValidEventDates AS
SELECT 
    EventDate,
    TimeZone,
    TRY_CAST(EventDate AS DATETIME) AS ParsedDate
FROM 
    EventDates
WHERE 
    IsValidDate = 'True' AND TRY_CAST(EventDate AS DATETIME) IS NOT NULL;
    
--Aggregate the number of visits and total minutes per month from the customer visit dataset.
SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CustomerVisitStart), 0) AS MonthStart,
    COUNT(*) AS NumberOfVisits,
    SUM(AmenityUseInMinutes) AS TotalMinutes,
    AVG(CAST(AmenityUseInMinutes AS FLOAT)) AS AvgMinutesPerVisit
FROM 
    CustomerVisits
WHERE 
    CustomerVisitStart >= '2020-01-01'
GROUP BY 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CustomerVisitStart), 0)
ORDER BY 
    MonthStart; GO

--Ranked visits by duration within each day using the customer visit dataset (window function).
SELECT 
    CustomerID,
    CustomerVisitStart,
    AmenityUseInMinutes,
    ROW_NUMBER() OVER (
        PARTITION BY CAST(CustomerVisitStart AS DATE) 
        ORDER BY AmenityUseInMinutes DESC
    ) AS VisitRank
FROM 
    CustomerVisits
WHERE 
    CustomerVisitStart BETWEEN '2020-06-01' AND '2020-06-30'
ORDER BY 
    CAST(CustomerVisitStart AS DATE), VisitRank; GO

-- Calculated a running total of focus group incidents over time from the incidents dataset.
SELECT 
    IncidentDate,
    NumberOfIncidents,
    SUM(NumberOfIncidents) OVER (
        ORDER BY IncidentDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM 
    Incidents
WHERE 
    IncidentDate BETWEEN '2019-07-01' AND '2019-07-31'
ORDER BY 
    IncidentDate; GO

-- Used the monthly visit summary dataset, compare each customer’s visits to the previous month with LAG.
SELECT 
    CustomerID,
    MonthStartDate,
    NumberOfVisits,
    LAG(NumberOfVisits) OVER (
        PARTITION BY CustomerID 
        ORDER BY MonthStartDate
    ) AS PreviousMonthVisits,
    NumberOfVisits - LAG(NumberOfVisits) OVER (
        PARTITION BY CustomerID 
        ORDER BY MonthStartDate
    ) AS VisitChange
FROM 
    MonthlyVisits
WHERE 
    MonthStartDate BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY 
    CustomerID, MonthStartDate; GO