Get the latest full week’s data for analysis in SQL

I was given sales data, where I have items and sales on a particular date. Now, the company wants to analyze the latest full week’s data against the total sales of company.

Item    date           Sales
Apple   08/25/2020       10
Orange 08/24/2020        20
Orange 08/21/2020        30

Now the full week is defined by a complete week from Sunday-Saturday. In the above made up example, it is clear that, these two data Apple 08/25/2020 10 Orange 08/24/2020 20 are from days Friday and Thursday respectively, so it is not a full week, hence we cannot take this week’s data. We need to check the last week’s data which would be for 08/21/2020 I was given 10 minutes to think on this, my immediate solution was, find the weekday number for the maximum data in the table. And subtract it from 7. If that is equal to 0 then we have a full week, and we can take the max date as the end date of our analysis and use a dateadd() to subtract 7 days from the max date to make it a start date. If I have something other than the 0, for example 6, then I use dateadd to go 6 days prior to my max date and use it as end date, again go 7 days behind this and get the start date.

CREATE TABLE SALES(Item nvarchar(10), dates date, Sales Numeric)

INSERT INTO SALES VALUES('Apple',CAST('08/25/2020' AS DATE),10),
('Orange',CAST('08/24/2020' AS DATE),20),
('Orange',CAST('08/21/2020' AS DATE),30)


WITH end_dates AS
(
SELECT CASE WHEN 7-DATEPART(dw, max(dates))=0 THEN max(dates)
ELSE DATEADD(day,- DATEPART(dw, max(dates)),max(dates)) END AS end_date
FROM SALES
),
Full_Week_Date AS
(
SELECT DATEADD(day,-6,end_date) as start_date ,end_date FROM end_dates
)
SELECT (SELECT SUM(SALES.sales)*100 FROM SALES JOIN Full_Week_Date ON(dates BETWEEN start_date AND end_date))/(SELECT SUM(SALES.sales) FROM SALES)  AS revenue_per

This is the best I could think of, but the interviewer said, given a large amount of data, this would run like forever. What would be an optimum solution for this problem? I only want to know, how to get start and end date of the week that I want to analyze. Rest the revenue and % revenue will be fairly easy I believe if I have this in place.

Answer

In an actual database the query to use will depend on indexes and other things. For a basic answer, there are a few things to consider here.

They state “a complete week from Sunday-Saturday”. Unless you are reporting at 11:59PM on Saturday you never will really have that full weeks sales in the same week. Since that is the case there is no reason to do all the checks you mentioned. They will cause unnecessary processing.

One thing you didn’t mention is if the total company sales included the week your sales you are checking are for. I am going to assume they want to exclude that weeks sales.

I am not going to claim this is the most efficient way, but I would do it like this.

INSERT INTO #Sales 
VALUES 
    ('Apple', '08/25/2020', 10),
    ('Orange', '08/24/2020', 20),
    ('Orange', '08/21/2020', 30),
    ('Apple', '11/14/2020', 25);

-- Get week to check (last week)
DECLARE @curWeek int = DATEPART(WW, DATEADD(wk, -1, GETDATE()));

-- Get Sales
SELECT 
    SUM(COALESCE(SalesAmt, 0)) AS CompanySales
    , (SELECT SUM(COALESCE(SalesAmt, 0)) FROM #Sales WHERE DATEPART(WW, SalesDate) = @curWeek) AS WeekSales
FROM #Sales
WHERE DATEPART(WW, SalesDate) <= @curWeek;