Page Comparison

...

Code Block

# top 20 downloaders by count(filehandle_id)
select fhdr.USER_ID, count(*) as c
from FILE_HANDLE_DOWNLOAD_RECORD fhdr
where fhdr.TIMESTAMP between unix_timestamp('2019-07-01 00:00:00')*1000 and unix_timestamp('2019-09-10 00:00:00')*1000
group by fhdr.USER_ID
order by c desc
limit 20;

MD5 duplicates

Code Block

language	sql

create table auditdb.fhd_detail2 as
select ls.ID, ls.VERSION_NUMBER, ls.NAME,
       ls.PROJECT_ID, ls.PARENT_ID, ls.BENEFACTOR_ID,
       ls.IS_PUBLIC, ls.IS_CONTROLLED, ls.IS_RESTRICTED,
       ls.FILE_HANDLE_ID, md5c.CONTENT_MD5, md5c.c as DUP_COUNT
from auditdb.latest_snapshot_202003 ls
join warehouse.FILE_HANDLE_RECORD fhr on fhr.ID=ls.FILE_HANDLE_ID
join auditdb.fhr_md5_count md5c on md5c.CONTENT_MD5=fhr.CONTENT_MD5
where ls.IS_PUBLIC=1 and (ls.IS_CONTROLLED=1 or ls.IS_RESTRICTED=1)
select fhdd.CONTENT_MD5 as MD5, fhdd.PROJECT_ID as SOURCE_PROJECT, fhdd.ID as SOURCE_ID, fhdd.VERSION_NUMBER as SOURCE_VERSION, fhdd.IS_PUBLIC as SOURCE_IS_PUBLIC, fhdd.IS_CONTROLLED as SOURCE_IS_CONTROLLED, fhdd.IS_RESTRICTED as SOURCE_IS_RESTRICTED,
       ls.PROJECT_ID as DUP_PROJECT, ls.ID as DUP_ID, ls.CREATED_BY, ls.IS_PUBLIC as DUP_IS_PUBLIC, ls.IS_CONTROLLED as DUP_IS_CONTROLLED, ls.IS_RESTRICTED as DUP_IS_RESTRICTED
from auditdb.fhd_detail2 fhdd
join FILE_HANDLE_RECORD fhr on fhr.CONTENT_MD5=fhdd.CONTENT_MD5
join auditdb.latest_snapshot_202003 ls on ls.FILE_HANDLE_ID=fhr.ID
where fhdd.FILE_HANDLE_ID <> fhr.ID and ls.IS_PUBLIC = 1 and (fhdd.IS_CONTROLLED <> ls.IS_CONTROLLED or fhdd.IS_RESTRICTED <> ls.IS_RESTRICTED)

Versions Compared

Old Version 2

New Version 3

Key

MD5 duplicates