How to Comparison between 2 groups-CodePudding

I have tasked with given Data and 3 table; Sales, Dates and Stores so far I have couple choosen KPI's to answer given Business questions, however I am stuck with efficiency of this query also I need couple feedback.

My question is In Store table there is store_group inside of that column there is store_group [A] AND [B] I want to sort this as sum of store_group A and store_group B so that I can make another calculation such as difference between A and B to assume A/B successful in Sales, also in my query I made a logical calculation that finds growth(next year growth) per previous column data-NextYearGrowth depending on previous date- I will add percentage to it so it will be better?

I appreciate feedbacks and suggestions

DDL

CREATE TABLE [dbo].[dates4](
    [date_id] [datetime2](7) NOT NULL,
    [calendar_month] [int] NOT NULL,
    [year] [int] NOT NULL,
    [iso_week] [int] NOT NULL,
    [iso_period] [int] NOT NULL,
 CONSTRAINT [PK_dates4] PRIMARY KEY CLUSTERED 
(
    [date_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[sales](
    [store_id] [int] NULL,
    [date_id] [datetime2](7) NOT NULL,
    [baskets] [int] NULL,
    [spend] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[store](
    [store_id] [int] NOT NULL,
    [store_group] [nvarchar](50) NULL,
 CONSTRAINT [PK_store] PRIMARY KEY CLUSTERED 
(
    [store_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

QUERY THAT needs to be 9x column 9x row

    SELECT DISTINCT
    (SELECT top 1 AVG(store_id) FROM store WHERE store_group = 'A' ) as SumStore_A ,
    Sl.baskets,
    SL.spend -LAG (SL.spend) OVER (ORDER BY Calendar_Month ASC) AS SpendGrowth,
    LEAD (SL.spend, 12) OVER (ORDER BY DS.date_id ASC) AS NextYearGrowth,
    CASE 
        WHEN SL.spend >= 1000 AND SL.spend <= 5000  THEN 'Low'
        WHEN SL.spend >= 5000 AND sl.spend <= 15000 THEN 'Moderate'
        ELSE 'High'
        END as SpendGroups,
    ST.store_group,
     DS.date_id,
    DS.calendar_month,
    DS.iso_week,
    DS.iso_period
FROM sales SL
    INNER JOIN store ST 
        on ST.store_id = SL.store_id
    INNER JOIN dates4 DS
        on ds.date_id = SL.date_id
    GROUP BY 
    SL.store_id,
    Sl.baskets,
     CASE 
        WHEN SL.spend >= 1000 AND SL.spend <= 5000  THEN 'Low'
        WHEN SL.spend >= 5000 AND sl.spend <= 15000 THEN 'Moderate'
        ELSE 'High'
        END,
        SL.spend,
        DS.calendar_month,
        DS.iso_period,
        DS.date_id,
        ST.store_group,
        DS.iso_week

output result there you can see my data, and store_group data thats what I need to sort something like:

|Store A | Store B| 
Sum(Data) Sum(Data)

PS. I need to work on my code, any other suggestions would be amazing. I was thinking inside of INNER JOIN using calculations such as SUM store_group in Store Table(nested query inside inner join) that will be much easy to execute if we have huge data and 1 select statement?

CodePudding user response：

I have find a way, way better.

SELECT M.calendar_month,
            totalbasketsA,
            totalspendA, 
            totalspendA/totalbasketsA AS AVgBasketA,
            totalbasketsB,
            totalspendB, 
            totalspendB/totalbasketsB AS AVgBasketB,
            basketDiff,
            spenddiff FROM 
            (SELECT T1.calendar_month,T1.totalbasketsA,T1.totalspendA,t2.totalbasketsB,t2.totalspendB, T1.totalbasketsA - T2.totalbasketsB AS basketDiff, T1.totalspendA-t2.totalspendB AS spenddiff FROM 
                    (SELECT SUM(spend) totalspendA, SUM(baskets) totalbasketsA, store_group, calendar_month from sales S1
                            INNER JOIN store S2 ON S1.store_id=S2.store_id
                            INNER JOIN dates4 S3 ON S3.date_id= S1.date_id
                                WHERE store_group='A'
                    GROUP BY store_group,calendar_month )  AS T1
     -- T1 for store A result set that is grouped by store_group and calendar_month
                        INNER JOIN 
     
      
                (SELECT SUM(spend) totalspendB, SUM(baskets) totalbasketsB, store_group, calendar_month from sales S1
                        INNER JOIN store S2 ON S1.store_id=S2.store_id
                        INNER JOIN dates4 S3 ON S3.date_id= S1.date_id
                            WHERE store_group='B'
                    GROUP BY store_group,calendar_month )   AS T2 ON T1.calendar_month = T2.calendar_month  ) M