November 5, 2018 at 10:39 am
I am fetching the number of clicks and first click from number of devices using event table and click table.
create table event
(
eventdate date,
emailid varchar(200),
sendid int,
listid int
);
insert into event values('2018-12-20','lookforkumar@gmail.com',22,100);
insert into event values('2018-12-21','lookforkumar@gmail.com',22,100);
insert into event values('2018-12-22','lookforsaravana@gmail.com',23,100);
insert into event values('2018-12-23','lookforsaravana@gmail.com',23,100);
create table click
(
eventid date,
emailid varchar(200),
sendid int,
listid int,
url_description varchar(200),
click_description varchar(200)
);
insert into click values('2018-12-20','lookforkumar@gmail.com',22,100,'PC','FLIPKART');
insert into click values('2018-12-21','lookforkumar@gmail.com',22,100,'SMARTPHONE','FLIPKART');
insert into click values('2018-12-22','lookforsaravana@gmail.com',23,100,'IPHONE','AMAZON');
insert into click values('2018-12-23','lookforsaravana@gmail.com',23,100,'PC','FLIPKART');
What I tried so far
Select evnt.eventdate,evnt.emailid,evnt.sendid,evnt.listid,firstclk,numberofclick
from
event evnt
left outer join
(
select emailid,sendid,min(eventid) as firstclk,count(eventid) as numberofclick
from click
group by emailid,sendid
) clk
on evnt.emailid=clk.emailid
and evnt.sendid=clk.sendid
Output I am getting
| eventdate | emailid | sendid | listid | firstclk | numberofclick |
| 20-12-18 0:00 | lookforkumar@gmail.com | 22 | 100 | 20-12-18 0:00 | 2 |
| 21-12-18 0:00 | lookforkumar@gmail.com | 22 | 100 | 20-12-18 0:00 | 2 |
| 22-12-18 0:00 | lookforsaravana@gmail.com | 23 | 100 | 22-12-18 0:00 | 2 |
| 23-12-18 0:00 | lookforsaravana@gmail.com | 23 | 100 | 22-12-18 0:00 | 2 |
Rules to get urldescription and click description columns.
1) Get urldescription from click table for min(eventdate) for each emailid and sendid combinations
2) Get click_description from click table for min(eventdate) for each emailid and sendid combinations
Expected Output :
| eventdate | emailid | sendid | listid | firstclk | numberofclick | url_description | click_description |
| 20-12-18 0:00 | lookforkumar@gmail.com | 22 | 100 | 20-12-18 0:00 | 2 | PC | Flipkart |
| 22-12-18 0:00 | lookforsaravana@gmail.com | 23 | 100 | 22-12-18 0:00 | 2 | IPHONE | AMAZON |
couple of quick question:
1) Is it possible to get expected results for url_description and click_description columns without using self joins.
2) Is it possible to get expected results for url_description and click_description columns using Over() (analytical )functions. Will I be able to eliminate duplicate records if I use self join to get url_description and click_description column value .
Saravanan
November 5, 2018 at 11:19 am
.
November 5, 2018 at 11:28 am
I don't fully unserstand your requirements from your description but yes you can get the same result set without doing a self join and by using some window functions.
All the data you need appeared to be in the one table so I excluded the other. Again...not sure of the design but here is an example for you.
create table #click
(
eventid date,
emailid varchar(200),
sendid int,
listid int,
url_description varchar(200),
click_description varchar(200)
);
insert into #click values('2018-12-20','lookforkumar@gmail.com',22,100,'PC','FLIPKART');
insert into #click values('2018-12-21','lookforkumar@gmail.com',22,100,'SMARTPHONE','FLIPKART');
insert into #click values('2018-12-22','lookforsaravana@gmail.com',23,100,'IPHONE','AMAZON');
insert into #click values('2018-12-23','lookforsaravana@gmail.com',23,100,'PC','FLIPKART');
SELECT
eventid,
emailid,
sendid,
listid,
MIN(eventid) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS firstclick,
COUNT(*) OVER (PARTITION BY emailid, sendid, listid) AS numberofclick
FROM
#click
GROUP BY
eventid,
emailid,
sendid,
listid
DROP TABLE #click
Cheers,
November 5, 2018 at 7:29 pm
Y.B. - Monday, November 5, 2018 11:28 AMI don't fully unserstand your requirements from your description but yes you can get the same result set without doing a self join and by using some window functions.All the data you need appeared to be in the one table so I excluded the other. Again...not sure of the design but here is an example for you.
create table #click
(
eventid date,
emailid varchar(200),
sendid int,
listid int,
url_description varchar(200),
click_description varchar(200)
);insert into #click values('2018-12-20','lookforkumar@gmail.com',22,100,'PC','FLIPKART');
insert into #click values('2018-12-21','lookforkumar@gmail.com',22,100,'SMARTPHONE','FLIPKART');
insert into #click values('2018-12-22','lookforsaravana@gmail.com',23,100,'IPHONE','AMAZON');
insert into #click values('2018-12-23','lookforsaravana@gmail.com',23,100,'PC','FLIPKART');SELECT
eventid,
emailid,
sendid,
listid,
MIN(eventid) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS firstclick,
COUNT(*) OVER (PARTITION BY emailid, sendid, listid) AS numberofclickFROM
#clickGROUP BY
eventid,
emailid,
sendid,
listidDROP TABLE #click
Cheers,
Myself able to get firstclick and number of clicks columns correctly. I want to fetch url_description and click_description columns
Saravanan
November 5, 2018 at 7:29 pm
Y.B. - Monday, November 5, 2018 11:28 AMI don't fully unserstand your requirements from your description but yes you can get the same result set without doing a self join and by using some window functions.All the data you need appeared to be in the one table so I excluded the other. Again...not sure of the design but here is an example for you.
create table #click
(
eventid date,
emailid varchar(200),
sendid int,
listid int,
url_description varchar(200),
click_description varchar(200)
);insert into #click values('2018-12-20','lookforkumar@gmail.com',22,100,'PC','FLIPKART');
insert into #click values('2018-12-21','lookforkumar@gmail.com',22,100,'SMARTPHONE','FLIPKART');
insert into #click values('2018-12-22','lookforsaravana@gmail.com',23,100,'IPHONE','AMAZON');
insert into #click values('2018-12-23','lookforsaravana@gmail.com',23,100,'PC','FLIPKART');SELECT
eventid,
emailid,
sendid,
listid,
MIN(eventid) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS firstclick,
COUNT(*) OVER (PARTITION BY emailid, sendid, listid) AS numberofclickFROM
#clickGROUP BY
eventid,
emailid,
sendid,
listidDROP TABLE #click
Cheers,
Myself able to get firstclick and number of clicks columns correctly. I want to fetch url_description and click_description columns
Saravanan
November 5, 2018 at 9:35 pm
Sorry but I’m doing this on mobile right now. Just follow my example for the window function where I use MIN but instead use FIRST_VALUE and change the column name to the one you want I.e. url_description.
I believe that should get you what you are looking for since you only care about the values during the first visit it seems. Give it a try and see how you make out.
November 6, 2018 at 6:38 am
Ok, back in front of my computer. Also I didn't originally scroll down and see your expected output.
I omitted eventid for grouping purposes but it's the same as your first click in your expample anyway. If you really want to see it twice just simple copy the one window function and give it a different name. I'm still not convinced this is the best way of doing this but it matches your output and meets your requirements of using window functions and not using a self join.
create table #click
(
eventid date,
emailid varchar(200),
sendid int,
listid int,
url_description varchar(200),
click_description varchar(200)
);
insert into #click values('2018-12-20','lookforkumar@gmail.com',22,100,'PC','FLIPKART');
insert into #click values('2018-12-21','lookforkumar@gmail.com',22,100,'SMARTPHONE','FLIPKART');
insert into #click values('2018-12-22','lookforsaravana@gmail.com',23,100,'IPHONE','AMAZON');
insert into #click values('2018-12-23','lookforsaravana@gmail.com',23,100,'PC','FLIPKART');
SELECT DISTINCT
emailid,
sendid,
listid,
MIN(eventid) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS firstclick,
COUNT(*) OVER (PARTITION BY emailid, sendid, listid) AS numberofclick,
MIN(url_description) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS url_description,
MIN(click_description) OVER (PARTITION BY emailid, sendid, listid ORDER BY eventid) AS click_description
FROM
#click
GROUP BY
eventid,
emailid,
sendid,
listid,
url_description,
click_description
DROP TABLE #click
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply