MySQL

create table t1(
   data_vihoda datetime NOT NULL,
   tutorial_author varchar(40) ,
   PRIMARY KEY ( data_vihoda )
);

insert into t1 
values ('11-02-19', 'asdasda'),
('11-02-20', 'asda')

;

drop table t2;

select * from t1;

create table t2(
   data_oper datetime NOT NULL,
   fl_sotr int ,
   PRIMARY KEY ( data_oper )
);

insert into t2 
values ('11-02-19', 0),
('11-05-23', 1),
('11-03-23', 3),
('11-07-23', 4)
;

select * from t2;

select * 
from t1 join t2 on 1=1
where fl_sotr > 0;

select sum(fl_sotr) over()
from t2;


drop table count_sovp;

create table count_sovp(
   data_vihoda datetime NOT NULL,
   tutorial_author varchar(40)
);

insert into count_sovp 
values ('11-02-19', 'asdasda'),
('11-02-20', 'asda')
;

insert into count_sovp 
values ('11-02-19', 'asdasda')
;

select * from count_sovp;


WITH my_col AS (
SELECT COLUMN_NAME 
FROM information_schema.columns 
WHERE table_schema='d' AND table_name='count_sovp'
) 
select sum(NoOfDuplicates) from (
SELECT count(*)-1 as NoOfDuplicates
FROM count_sovp 

group by (select * from my_col)

) a


;


SELECT COUNT(*) AS NoOfOccurrences
FROM count_sovp 
GROUP BY    (SELECT column_name
            FROM user_tab_cols
            WHERE table_name = 'count_sovp'
            )



HAVING COUNT(*) > 1
;


SELECT *, row_number() over (partition by *) rownum
FROM count_sovp

;

SELECT COLUMN_NAME 
FROM information_schema.columns 
WHERE table_schema='d' AND table_name='count_sovp'
;

WITH my_col AS (
SELECT COLUMN_NAME 
FROM information_schema.columns 
WHERE table_schema='d' AND table_name='count_sovp'
) 



DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    a  INT, 
    b  INT
);

INSERT INTO
    t1(a,b)
VALUES
    (1,1),
    (1,2),
    (1,3),
    (2,1),
    (1,2),
    (1,3),
    (2,1),
    (2,2);
    
    
    WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY a,b) rownum
    FROM 
        t1
) 
SELECT 
  * 
FROM 
    cte 
WHERE 
    rownum > 1;
    
    
    
SELECT column_name
FROM user_tab_cols
WHERE table_name = 'count_sovp';


create table users_hits (
    user_id INT,
    url VARCHAR(30) NOT NULL,
    timestamp DATETIME
);

INSERT INTO
    users_hits(user_id, url, timestamp)
VALUES
    (1, 'aa','20-02-01'),
    (1, 'https://music.yandex.ru/feed','20-02-07 11:49:04'),
    (1, 'https://music.yandex.ru/feed','20-02-01 16:49:04'),
    (2, 'bb','20-02-03'),
    (3, 'https://music.yandex.ru/feed','20-02-01'),
    (3, 'https://music.yandex.ru/feed','20-02-01'),
    (3, 'cc','20-02-19'),
    (5, 'aaa','20-02-07'),
    (5, 'ada','20-02-01'),
    (5, 'https://music.yandex.ru/feed','20-02-01'),
    (5, 'zaa','20-02-03 11:49:04'),
    (6, 'bbccbb','20-01-03')
    
;

drop table users_hits;


select distinct user_id
from users_hits
where user_id in
                (select distinct user_id
                from users_hits
                where TIMESTAMPDIFF(second, timestamp, NOW() )/(60*60*24) <=7
                )
      and user_id not in
                (select distinct user_id
                from users_hits
                where TIMESTAMPDIFF(minute, timestamp, NOW() )/(60*24) <=7
                        and url = 'https://music.yandex.ru/feed'
                )
;