.mode csv
.header off
CREATE TABLE WebsiteAccess(
ts TIMESTAMP,
user_id VARCHAR(50),
country_id VARCHAR(50),
site_id VARCHAR(50)
);
.import "SWE sample data - Q3 data.csv" WebsiteAccess
-- Delete imported header line
DELETE FROM WebsiteAccess WHERE ts = 'ts';
-- Question 1
SELECT site_id, COUNT(DISTINCT user_id) AS user_count
FROM WebsiteAccess
WHERE country_id = "BDV"
GROUP BY site_id
ORDER BY user_count DESC
LIMIT 1;
-- Question 2
SELECT user_id, site_id, COUNT(*) AS number_of_visits
FROM WebsiteAccess
WHERE ts BETWEEN "2019-02-03 00:00:00" AND "2019-02-04 23:59:59"
GROUP BY user_id, site_id
HAVING number_of_visits > 10;
-- Question 3
WITH LastVisitTime AS (
SELECT user_id, MAX(ts) AS ts
FROM WebsiteAccess
GROUP BY user_id
)
SELECT site_id, COUNT(site_id) AS number_users
FROM LastVisitTime lvt
JOIN WebsiteAccess wa
ON lvt.ts = wa.ts and lvt.user_id = wa.user_id
GROUP BY site_id
ORDER BY number_users DESC;
-- Question 4
WITH LastVisitSite AS (
SELECT wa.user_id, wa.site_id
FROM WebsiteAccess wa
JOIN (
SELECT user_id, MAX(ts) AS ts
FROM WebsiteAccess
GROUP BY user_id) lvt -- LastVisitTime
ON wa.ts = lvt.ts AND wa.user_id = lvt.user_id
),
FirstVisitSite AS (
SELECT wa.user_id, wa.site_id
FROM WebsiteAccess wa
JOIN (
SELECT user_id, MIN(ts) AS ts
FROM WebsiteAccess
GROUP BY user_id) fvt -- FirstVisitTime
ON wa.ts = fvt.ts AND wa.user_id = fvt.user_id
)
SELECT COUNT(*)
FROM LastVisitSite lvs, FirstVisitSite fvs
WHERE lvs.user_id = fvs.user_id AND lvs.site_id = fvs.site_id;
Lm1vZGUgY3N2Ci5oZWFkZXIgb2ZmCgpDUkVBVEUgVEFCTEUgV2Vic2l0ZUFjY2VzcygKICB0cyBUSU1FU1RBTVAsCiAgdXNlcl9pZCBWQVJDSEFSKDUwKSwKICBjb3VudHJ5X2lkIFZBUkNIQVIoNTApLAogIHNpdGVfaWQgVkFSQ0hBUig1MCkKKTsKCi5pbXBvcnQgIlNXRSBzYW1wbGUgZGF0YSAtIFEzIGRhdGEuY3N2IiBXZWJzaXRlQWNjZXNzCgotLSBEZWxldGUgaW1wb3J0ZWQgaGVhZGVyIGxpbmUKREVMRVRFIEZST00gV2Vic2l0ZUFjY2VzcyBXSEVSRSB0cyA9ICd0cyc7CgotLSBRdWVzdGlvbiAxClNFTEVDVCBzaXRlX2lkLCBDT1VOVChESVNUSU5DVCB1c2VyX2lkKSBBUyB1c2VyX2NvdW50CkZST00gV2Vic2l0ZUFjY2VzcwpXSEVSRSBjb3VudHJ5X2lkID0gIkJEViIKR1JPVVAgQlkgc2l0ZV9pZApPUkRFUiBCWSB1c2VyX2NvdW50IERFU0MKTElNSVQgMTsKCi0tIFF1ZXN0aW9uIDIKU0VMRUNUIHVzZXJfaWQsIHNpdGVfaWQsIENPVU5UKCopIEFTIG51bWJlcl9vZl92aXNpdHMKRlJPTSBXZWJzaXRlQWNjZXNzCldIRVJFIHRzIEJFVFdFRU4gIjIwMTktMDItMDMgMDA6MDA6MDAiIEFORCAiMjAxOS0wMi0wNCAyMzo1OTo1OSIKR1JPVVAgQlkgdXNlcl9pZCwgc2l0ZV9pZApIQVZJTkcgbnVtYmVyX29mX3Zpc2l0cyA+IDEwOwoKCi0tIFF1ZXN0aW9uIDMKV0lUSCBMYXN0VmlzaXRUaW1lIEFTICgKICBTRUxFQ1QgdXNlcl9pZCwgTUFYKHRzKSBBUyB0cwogIEZST00gV2Vic2l0ZUFjY2VzcwogIEdST1VQIEJZIHVzZXJfaWQKKQpTRUxFQ1Qgc2l0ZV9pZCwgQ09VTlQoc2l0ZV9pZCkgQVMgbnVtYmVyX3VzZXJzCkZST00gTGFzdFZpc2l0VGltZSBsdnQKICBKT0lOIFdlYnNpdGVBY2Nlc3Mgd2EKICBPTiBsdnQudHMgPSB3YS50cyBhbmQgbHZ0LnVzZXJfaWQgPSB3YS51c2VyX2lkCkdST1VQIEJZIHNpdGVfaWQKT1JERVIgQlkgbnVtYmVyX3VzZXJzIERFU0M7CgotLSBRdWVzdGlvbiA0CldJVEggTGFzdFZpc2l0U2l0ZSBBUyAoCiAgU0VMRUNUIHdhLnVzZXJfaWQsIHdhLnNpdGVfaWQKICBGUk9NIFdlYnNpdGVBY2Nlc3Mgd2EKICAgIEpPSU4gKAogICAgICBTRUxFQ1QgdXNlcl9pZCwgTUFYKHRzKSBBUyB0cwogICAgICBGUk9NIFdlYnNpdGVBY2Nlc3MKICAgICAgR1JPVVAgQlkgdXNlcl9pZCkgbHZ0ICAtLSBMYXN0VmlzaXRUaW1lCiAgICBPTiB3YS50cyA9IGx2dC50cyBBTkQgd2EudXNlcl9pZCA9IGx2dC51c2VyX2lkCiksCkZpcnN0VmlzaXRTaXRlIEFTICgKICBTRUxFQ1Qgd2EudXNlcl9pZCwgd2Euc2l0ZV9pZAogIEZST00gV2Vic2l0ZUFjY2VzcyB3YQogICAgSk9JTiAoCiAgICAgIFNFTEVDVCB1c2VyX2lkLCBNSU4odHMpIEFTIHRzCiAgICAgIEZST00gV2Vic2l0ZUFjY2VzcwogICAgICBHUk9VUCBCWSB1c2VyX2lkKSBmdnQgLS0gRmlyc3RWaXNpdFRpbWUKICAgIE9OIHdhLnRzID0gZnZ0LnRzIEFORCB3YS51c2VyX2lkID0gZnZ0LnVzZXJfaWQKKQpTRUxFQ1QgQ09VTlQoKikKRlJPTSBMYXN0VmlzaXRTaXRlIGx2cywgRmlyc3RWaXNpdFNpdGUgZnZzCldIRVJFIGx2cy51c2VyX2lkID0gZnZzLnVzZXJfaWQgQU5EIGx2cy5zaXRlX2lkID0gZnZzLnNpdGVfaWQ7Cgo=