#### Topic: on unique entrance

There is a table

``````Date Platform ID
2016-01 Test1 1
2016-01 Test1 2
2016-01 Test2 1
2016-01 Test2 2
2016-01 Test3 2
2016-01 Test3 2
2016-01 Test3 1
2016-01 Test3 3
2016-01 Test4 1
2016-01 Test4 2
2016-01 Test4 3
2016-01 Test4 4
2016-01 Test4 5
2016-01 Test4 6``````

I should deduce an amount unique ID which enter in groups Test4 and nearby an amount of unique values which enter only in Test4 (and a case in point)
That is on an output should be

``Test4 |6 | 3``

Well it is possible also other data from groupings to deduce
To me at the main thing to look as to receive which enter only in Test4

#### Re: on unique entrance

-=Koba =-;

``````with t (dt_chr, platform, id) as
(
select ' 2016-01 ', ' Test1 ', 1 from dual union all
select ' 2016-01 ', ' Test1 ', 2 from dual union all
select ' 2016-01 ', ' Test2 ', 1 from dual union all
select ' 2016-01 ', ' Test2 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 1 from dual union all
select ' 2016-01 ', ' Test3 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 1 from dual union all
select ' 2016-01 ', ' Test4 ', 2 from dual union all
select ' 2016-01 ', ' Test4 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 4 from dual union all
select ' 2016-01 ', ' Test4 ', 5 from dual union all
select ' 2016-01 ', ' Test4 ', 6 from dual
);
t2 (id) as
(
select id from t where platform &lt;&gt; ' Test4'
)
select t.platform, count (distinct t.id), count (distinct t2.id)
from t
left join t2 on t.id = t2.id
where platform = ' Test4'
group by platform;``````

#### Re: on unique entrance

I thank for the help in a direction

#### Re: on unique entrance

Down ;

wrote:

To me at the main thing to look as to receive which enter only in Test4

.....
stax

Stax?

#### Re: on unique entrance

Whether and it is possible to superimpose the given decision here?

``````/* Formatted on 10/12/2017 OF 3:01:06 PM (QP5 v5.313) */
WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT operation;
SUM (a);
SUM (b);
SUM (c);
SUM (d)
FROM (SELECT *
FROM ( SELECT operation_time AS operation, platform, USER_ID
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
GROUP BY GROUPING SETS ((operation_time, PLATFORM, USER_ID), (PLATFORM, USER_ID)))
PIVOT
(COUNT (*) FOR platform IN (' Test1 ' AS A, ' Test2 ' AS B, ' Test3 ' AS A C, ' Test4 ' AS D)))
GROUP BY operation
ORDER BY operation``````
``````2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 1
2016-06 0 0 0 1
2 2 3 6``````

Here count an amount of unique entrances in a platform for the period
5 column for test4 is necessary to me

``````2016-01 1 1 1 1 0
2016-02 1 1 1 1 0
2016-03 0 0 1 1 0
2016-04 0 0 1 1 1
2016-05 0 0 0 1 1
2016-06 0 0 0 1 1
2 2 3 6 3``````

At test4 during 2016-01, 2016-02, 2016-03 enter into other groups, are not considered,
And 2016-04 2016-05, 2016-06 already approach

#### Re: on unique entrance

-=Koba =-;
Task setting is formulated.... Not precisely enough
Something is necessary such?

``````WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT platform, count (distinct USER_ID) cnt_for_all;
count (distinct
case when platform! = ' Test4 ' or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ') then null
else USER_ID end
) cnt_for_4
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
group by platform``````

#### Re: on unique entrance

MaximaXXL wrote:

- =Koba =-;
Task setting is formulated.... Not precisely enough
Something is necessary such?

``````WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT platform, count (distinct USER_ID) cnt_for_all;
count (distinct
case when platform! = ' Test4 ' or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ') then null
else USER_ID end
) cnt_for_4
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
group by platform``````

Yes but at you a binding
or operation_time in (' 2016-01 ', ' 2016-02 ', ' 2016-03 ')

#### Re: on unique entrance

``````t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)``````

I should count number of unique entrances in platform on the periods

``````2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 1
2016-06 0 0 0 1
2 2 3 6``````

One more column for test4 too the most unique value in platform on the periods, but plus still a condition that USER_ID that  for this period did not appear anywhere is necessary to me
Satisfies

``````SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL``````

#### Re: on unique entrance

All subject is filled up  from . Where that unique full and consistent post from which it is possible to understand what is required to the author?

#### Re: on unique entrance

All subject is filled up  from . Where that unique full and consistent post from which it is possible to understand what is required to the author?

Last before you

#### Re: on unique entrance

wrote:

Stax?

``````with t (dt_chr, platform, id) as
(
select ' 2016-01 ', ' Test1 ', 1 from dual union all
select ' 2016-01 ', ' Test1 ', 2 from dual union all
select ' 2016-01 ', ' Test2 ', 1 from dual union all
select ' 2016-01 ', ' Test2 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 2 from dual union all
select ' 2016-01 ', ' Test3 ', 1 from dual union all
select ' 2016-01 ', ' Test3 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 1 from dual union all
select ' 2016-01 ', ' Test4 ', 2 from dual union all
select ' 2016-01 ', ' Test4 ', 3 from dual union all
select ' 2016-01 ', ' Test4 ', 4 from dual union all
select ' 2016-01 ', ' Test4 ', 5 from dual union all
select ' 2016-01 ', ' Test4 ', 7 from dual union all
select ' 2016-01 ', ' Test4 ', 7 from dual union all
select ' 2016-01 ', ' Test4 ', 8 from dual union all
select ' 2016-01 ', ' Test4 ', 6 from dual
);
t2 (id) as
(
select id from t where platform &lt;&gt; ' Test4'
)
select t.platform, count (distinct t.id), count (distinct t2.id)
from t
left join t2 on t.id = t2.id
where platform = ' Test4'
group by platform
/
PLATF COUNT (DISTINCTT.ID) COUNT (DISTINCTT2.ID)
----- ------------------- --------------------
Test4 8 3``````

I so understood that is necessary 7 instead of 5
.....
stax

#### Re: on unique entrance

-=Koba =-;
Count separately and  to
ps
On  the index is?
......
stax

#### Re: on unique entrance

Yes so it is exact

``````/* Formatted on 10/12/2017 OF 4:14:30 PM (QP5 v5.313) */
WITH
t (id, operation_time, platform, USER_ID) AS
(SELECT 1, ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT 2, ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT 3, ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT 4, ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT 5, ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT 6, ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT 7, ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT 8, ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT 9, ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT 10, ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT 11, ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT 12, ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT 13, ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT 14, ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
SELECT *
FROM t``````

#### Re: on unique entrance

Stax wrote:

- =Koba =-;
Count separately and  to
ps
On  the index is?
......
stax

So it and the question how to check up that for the given period user_id except test4 is not present at other platforms, on join I will already connect them

#### Re: on unique entrance

-=Koba =-;
,
N passes on , not optimally

``````WITH
t (operation_time, platform, USER_ID) AS
(SELECT ' 2016-01 ', ' Test1 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test1 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test2 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test2 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test3 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test3 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test3 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-01 ', ' Test4 ', 1 FROM DUAL
UNION ALL
SELECT ' 2016-02 ', ' Test4 ', 2 FROM DUAL
UNION ALL
SELECT ' 2016-03 ', ' Test4 ', 3 FROM DUAL
UNION ALL
SELECT ' 2016-04 ', ' Test4 ', 4 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 5 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 7 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 7 FROM DUAL
UNION ALL
SELECT ' 2016-05 ', ' Test4 ', 8 FROM DUAL
UNION ALL
SELECT ' 2016-06 ', ' Test4 ', 6 FROM DUAL)
,t4 as
(SELECT count (distinct user_id) c4 FROM t t1
WHERE platform IN (' Test4 ')
and not exists (select 1 from t t2 where t2.user_id=t1.user_id
and t2.platform IN (' Test1 ', ' Test2 ', ' Test3 '))
)
,p as (
SELECT operation;
SUM (a);
SUM (b);
SUM (c);
SUM (d)
FROM (SELECT *
FROM ( SELECT operation_time AS operation, platform, USER_ID
FROM t
WHERE platform IN (' Test1 ';
' Test2 ';
' Test3 ';
' Test4 ')
GROUP BY GROUPING SETS ((operation_time, PLATFORM, USER_ID), (PLATFORM, USER_ID)))
PIVOT
(COUNT (*) FOR platform IN (' Test1 ' AS A, ' Test2 ' AS B, ' Test3 ' AS A C, ' Test4 ' AS D)))
GROUP BY operation
)
select p. *, decode (operation, null, c4) c4 from p, t4
ORDER BY operation
/
SQL&gt; /
OPERATI SUM (A) SUM (B) SUM (C) SUM (D) C4
------- ---------- ---------- ---------- ---------- ----------
2016-01 1 1 1 1
2016-02 1 1 1 1
2016-03 0 0 1 1
2016-04 0 0 1 1
2016-05 0 0 0 3
2016-06 0 0 0 1
2 2 3 8 5
7 rows selected.``````

.....
stax

#### Re: on unique entrance

[quote = - = Koba =-] So it and the question how to check up that for the given period user_id except test4 is not present at other platforms, on join I will already connect them

``````select platform, count (*) from (
select user_id, min (platform) platform
from t
group by user_id
having count (distinct platform) = 1
) group by platform
;``````