Can't get rolling distinct count values as expected

Question

I am using a table as below:

Period	County	Quarter	company	product
01/01/2020	DE	01/01/2020	WKDM2	Product1
01/01/2020	DE	01/01/2020	2GFSDG37	Product1
01/02/2020	DE	01/01/2020	ORD56	Product2
01/03/2020	DE	01/01/2020	GFDS	Product3
01/03/2020	DE	01/01/2020	24GFDSGF2	Product1
01/03/2020	DE	01/01/2020	2GFSDG37	Product3
01/03/2020	DE	01/01/2020	24GSFD1	Product1
01/04/2020	DE	01/04/2020	2GFSDG37	Product4
01/04/2020	DE	01/04/2020	23GSFDG5	Product6
01/04/2020	DE	01/04/2020	24GSFD1	Product1
01/05/2020	DE	01/04/2020	23GSDF6	Product3
01/06/2020	DE	01/04/2020	24GSFD1	Product8

I tried to extract wanted data but this is not working as expected, i have bad count for Company Q & Product Q (code to reproduce):

CREATE OR REPLACE TEMPORARY TABLE "TMP_TEST" (
    "Period"  TIMESTAMP,
    "Country"     VARCHAR,
    "Quarter"    TIMESTAMP,
    "Company"    VARCHAR,
    "Product"    VARCHAR
);

INSERT INTO "TMP_TEST" 
VALUES 
('01/01/2020','DE           ','01/01/2020     ','WKDM2    ','Product1'),
('01/01/2020','DE           ','01/01/2020     ','2GFSDG37  ','Product1'),
('01/02/2020','DE           ','01/01/2020     ','ORD56    ','Product2'),
('01/03/2020','DE           ','01/01/2020     ','GFDS     ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','24GFDSGF2 ','Product1'),
('01/03/2020','DE           ','01/01/2020     ','2GFSDG37  ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','24GSFD1   ','Product1'),
('01/04/2020','DE           ','01/04/2020     ','2GFSDG37  ','Product4'),
('01/04/2020','DE           ','01/04/2020     ','23GSFDG5  ','Product6'),
('01/04/2020','DE           ','01/04/2020     ','24GSFD1   ','Product1'),
('01/05/2020','DE           ','01/04/2020     ','23GSDF6   ','Product3'),
('01/06/2020','DE           ','01/04/2020     ','24GSFD1   ','Product8');

Not working query:

SELECT t1."Period",t1."Country",t1."Quarter"
    ,COUNT(DISTINCT(t1."Company")) AS "Company",COUNT(DISTINCT(t1."Product")) AS "Product",
   (SELECT COUNT(DISTINCT("Company")) from "TMP_TEST" t2 where t1."Period" >= t2."Quarter" AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country")  AS "Company Q",
   (SELECT COUNT(DISTINCT("Product")) from "TMP_TEST" t3 where t1."Period" >= t3."Quarter" AND t1."Period" <= t3."Period" AND t1."Country" = t3."Country")  AS "Product Q"
   FROM "TMP_TEST" t1
   group by 1,2,3
   ORDER BY 1

Query working for 01/03/2020 :

SELECT COUNT(DISTINCT("Company")) from "TMP_TEST" WHERE "Period" IN('01/01/2020','01/02/2020','01/03/2020' )

The results I want :

Period	County	Quarter	Company	Product	Company Q	Product Q
2020-01-01	DE	2020-01-01	2	1	2	1
2020-01-02	DE	2020-01-01	1	1	3	2
2020-01-03	DE	2020-01-01	4	2	6	3
2020-01-04	DE	2020-01-04	3	3	3	3
2020-01-05	DE	2020-01-04	1	1	4	4
2020-01-06	DE	2020-01-04	1	1	4	5

For period 2020-02-01 (including 2020-02-01 and 2020-01-01 data), I need to find 3 unique companies (WKDM2,2GFSDG37,ORD56) and 2 unique Products (Product1,Product2)

For period 2020-03-01 (including 2020-03-01,2020-02-01 & 2020-01-01 data), I need to find 6 unique companies (WKDM2,2GFSDG37,ORD56,GFDS,24GFDSGF2,24GSFD1) and 3 unique Products (Product1,Product2,Product3).

Could you tell me were am I wrong please?

Update:

I created this working query which is overkill in terms of performance.

WITH M AS (
  SELECT t3."Period",t3."Country",t3."Quarter"
    ,COUNT(DISTINCT(t3."Company")) AS "Company"
   FROM "TMP_TEST" t3
   group by 1,2,3
   ORDER BY 1
), Q AS (
  SELECT COUNT(DISTINCT(t1."Company")) AS "Company Q",t2."Period",t2."Country" from "TMP_TEST" t2,"TMP_TEST" t1 where t1."Period" >= t2."Quarter" AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country"
GROUP BY t2."Period", t2."Country"
ORDER BY t2."Period"
), Y AS (
    SELECT COUNT(DISTINCT(t1."Company")) AS "Company Y",t2."Period",t2."Country" from "TMP_TEST" t2,"TMP_TEST" t1 where t1."Period" >= DATE_TRUNC('YEAR',t2."Quarter") AND t1."Period" <= t2."Period" AND t1."Country" = t2."Country"
    GROUP BY t2."Period", t2."Country"
    ORDER BY t2."Period"
)
SELECT M.*,Q."Company Q",Y."Company Y" from M,Q,Y WHERE M."Period" = Q."Period" AND M."Country" = Q."Country" AND M."Period" = Y."Period" AND M."Country" = Y."Country"

user_43992 · Answer

This solution feels over kill, but it does work.

WITH comp_q AS (
    SELECT country, quarter, Period, company
        ,MIN(period) OVER (PARTITION BY country, quarter, company) as m_p
    FROM TMP_TEST
), prod_q AS (
    SELECT country, quarter, Period, Product
        ,MIN(period) OVER (PARTITION BY country, quarter, Product) as m_p
    FROM TMP_TEST
)

SELECT Period
    ,Country
    ,Quarter
    ,Company
    ,Product
    ,SUM(comp_q) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as "Company Q"
    ,SUM(prod_q) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as "Product Q"
FROM (
    SELECT t1.Period::date AS Period
        ,t1.Country
        ,t1.Quarter::DATE AS Quarter
        ,COUNT(DISTINCT(t1.Company)) AS Company
        ,COUNT(DISTINCT(t1.Product)) AS Product
        ,COUNT(DISTINCT q.company) as comp_q
        ,COUNT(DISTINCT p.Product) as prod_q
    FROM TMP_TEST AS t1
    LEFT JOIN comp_q AS q ON t1.Country = q.Country AND t1.Quarter = q.Quarter AND t1.period = q.m_p
    LEFT JOIN prod_q AS p ON t1.Country = p.Country AND t1.Quarter = p.Quarter AND t1.period = p.m_p
    GROUP BY 1,2,3
)
ORDER BY 1;

So the two CTE are getting the earliest period per quarter that a company/product is present and then joins that, which is in effect allowing a distinct new company/products, which can then be summed in the outer layer.

It feels really gross.

PERIOD  COUNTRY QUARTER COMPANY PRODUCT Company Q   Product Q
2020-01-01  DE              2020-01-01  2   1   2   1
2020-01-02  DE              2020-01-01  1   1   3   2
2020-01-03  DE              2020-01-01  4   2   6   3
2020-01-04  DE              2020-01-04  3   3   3   3
2020-01-05  DE              2020-01-04  1   1   4   4
2020-01-06  DE              2020-01-04  1   1   4   5

Ok, so there is a cleaner way:

SELECT Period
    ,Country
    ,Quarter
    ,COUNT(DISTINCT(Company)) AS Company
    ,COUNT(DISTINCT(Product)) AS Product
    ,MAX(Company_s) as company_Q
    ,MAX(Product_s) as Product_q
FROM (
    SELECT *
        ,sum(iff(r_c=1, 1,0)) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as Company_s
        ,sum(iff(r_p=1, 1,0)) OVER (PARTITION BY Country,Quarter order by period rows between unbounded preceding and current row) as Product_s
    FROM ( 
        SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY Country,Quarter,company order by period ) as r_c
            ,ROW_NUMBER() OVER(PARTITION BY Country,Quarter,Product order by period ) as r_p
        FROM TMP_TEST
    )
)
GROUP BY 1,2,3
ORDER BY period;

This uses ROW_NUMBER to allocate a counter per country/quarter to each company/product, which we then can count only the first instance of (the IFF(r_c=1, 1,0) part) and then we can do a WINDOW FRAME accumulation of those values. This however is in periods so needs to be solved by the MAX on the outer layer:

user_8462809 · Answer

Thank you @Simeon, i added the year too and put it all together as below:

CREATE OR REPLACE TEMPORARY TABLE "TMP_TEST" (
    "Period"  TIMESTAMP,
    "Country"     VARCHAR,
    "Quarter"    TIMESTAMP,
    "First YD"    TIMESTAMP,
    "Company"    VARCHAR,
    "Product"    VARCHAR
);

INSERT INTO "TMP_TEST" 
VALUES 
('01/01/2020','DE           ','01/01/2020     ','01/01/2020   ','WKDM2    ','Product1'),
('01/01/2020','DE           ','01/01/2020     ','01/01/2020   ','2GFSDG37  ','Product1'),
('01/02/2020','DE           ','01/01/2020     ','01/01/2020   ','ORD56    ','Product2'),
('01/03/2020','DE           ','01/01/2020     ','01/01/2020   ','GFDS     ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','01/01/2020   ','24GFDSGF2 ','Product1'),
('01/03/2020','DE           ','01/01/2020     ','01/01/2020   ','2GFSDG37  ','Product3'),
('01/03/2020','DE           ','01/01/2020     ','01/01/2020   ','24GSFD1   ','Product1'),
('01/04/2020','DE           ','01/04/2020     ','01/01/2020   ','2GFSDG37  ','Product4'),
('01/04/2020','DE           ','01/04/2020     ','01/01/2020   ','23GSFDG5  ','Product6'),
('01/04/2020','DE           ','01/04/2020     ','01/01/2020   ','24GSFD1   ','Product1'),
('01/05/2020','DE           ','01/04/2020     ','01/01/2020   ','23GSDF6   ','Product3'),
('01/06/2020','DE           ','01/04/2020     ','01/01/2020   ','24GSFD1   ','Product8');

SELECT "Period"
    ,"Country"
    ,"Quarter"
    ,"First YD"
    ,COUNT(DISTINCT("Company")) AS "Company"
    ,COUNT(DISTINCT("Product")) AS "Product"
    ,MAX("Company_sq") as "company_Q"
    ,MAX("Product_sq") as "Product_q"
    ,MAX("Company_sy") as "company_Y"
    ,MAX("Product_sy") as "Product_q"
FROM (
    SELECT *
        ,sum(iff(rq_c=1, 1,0)) OVER (PARTITION BY "Country","Quarter" order by "Period" rows between unbounded preceding and current row) as "Company_sq"
        ,sum(iff(rq_p=1, 1,0)) OVER (PARTITION BY "Country","Quarter" order by "Period" rows between unbounded preceding and current row) as "Product_sq"
        ,sum(iff(ry_c=1, 1,0)) OVER (PARTITION BY "Country","First YD" order by "Period" rows between unbounded preceding and current row) as "Company_sy"
        ,sum(iff(ry_p=1, 1,0)) OVER (PARTITION BY "Country","First YD" order by "Period" rows between unbounded preceding and current row) as "Product_sy"
    FROM ( 
        SELECT *
            ,ROW_NUMBER() OVER(PARTITION BY "Country","Quarter","Company" order by "Period" ) as rq_c
            ,ROW_NUMBER() OVER(PARTITION BY "Country","Quarter","Product" order by "Period" ) as rq_p
            ,ROW_NUMBER() OVER(PARTITION BY "Country","First YD","Company" order by "Period" ) as ry_c
            ,ROW_NUMBER() OVER(PARTITION BY "Country","First YD","Product" order by "Period" ) as ry_p
        FROM TMP_TEST
    )
)
GROUP BY 1,2,3,4
ORDER BY "Period";

Can't get rolling distinct count values as expected

2 Answers