Aggregation Help

[Senor_Dai]

Hello

I have a table that contains the following:

user_id,

session_datetime,

ip_address

During the load, it it possible to flag all users that have more than 5 IP addresses in single day and also what day(s) that occurred?

Many thanks

Dai

[Digvijay_Singh]

Probably like this - 

Create new table load - 

counttable:

Load  user_id,

          Date(floor(session_datetime)) as session_date,

         count(distinct ip_address) as ipcount

resident yourtable;

Group By user_id, Date(floor(session_datetime))

;

Now left join above table with the main table, assuming user_id and session_date as common field is the common field, I think you would need session date field in the main table like I created above to remove time part from that for correct joining - 

Left Join(main table)

Load user_id, session_date,1 as 5IPFlag

resident counttable

where ipcount > 5;

I have not tested it but if it works correctly, you should see 5IPFlag as 1 in all the days row where 5 distinct IPs are used by a user.

Thanks,