i have data of the form:
day, hour, seller, buyer
i want to find all instances where a seller appears only on a single day and sells to more than 10 different buyers in the same hour.
in sql i would do:
select a.* from (select day, hour, seller, count(distinct buyer) as cnt_buy from sales group by day, hour, seller) a join (select seller, count(distinct day) as cnt_date from sales group by seller) b on a.seller=b.seller where cnt_buy>10 and cnt_date=1
clarification: I would like to combine the two following queries:
(1) find all sellers which appear only on single day:
| stats dc(day) as cnt_date by seller| where cnt_date=1
(2) find all instances where one seller sold to more than 10 buyers in
one hour:
| stats dc(buyer) as cnt_buy by day, hour, seller | where cnt_buy>10
now i want to join the above two so that only the sellers in (1) are used in (2). what would be an efficient
splunk way to do so?
... View more