Queries in CQL

1. Currency Conversion Query: Convert the prices of incoming bids from U.S. dollars into Euros.
   
   

   Select  itemID, DolToEuro(bid_price), bidderID
   From    Bid
   

   
   Comments: This query relies on two CQL defaults: the default Unbounded window on Bid stream, and the default outermost Istream operator. DolToEuro is an user-defined function to convert a dollar price to euro.
   
   
2. Selection Query: Select all bids on a specified set of 5 items.
   
   

   Select  itemID, bid_price
   From    Bid
   Where   itemID = 1007 or itemID = 1020 or itemID = 2001 or itemID = 2019
           or itemID = 1087
   

   
   
   
3. Local Item Suggestion Query: Report all items in category 10 that are put up for auction by some seller in Oregon.
   
   

   Select  Istream(P.name, P.city, O.itemID)
   From    OpenAuction [Now] O, Person P, Item I
   Where   O.sellerID = P.id and P.state = 'OR' and O.itemID = I.id 
           and I.categoryID = 10 
   

   
   Comments: This query returns items that were put up for auction by some seller who lived in Oregon when the auction started. Changes in the seller's address after the start of an auction will not affect the result of this query.
   
   
4. Open Auctions Query: Maintain a table of the currently open auctions.
   
   

   Select * 
   From   OpenAuction 
   Where  itemID Not In (Select itemID 
                         From   ClosedAuction)
   

   
   
   
5. Closing Price Query: Report the closing price of each auction.
   
   

   CurrentPrice: 
       Select    P.itemID, Max(P.price) as price
       From      ((Select itemID, bid_price as price 
                   From   Bid) Union All
                  (Select itemID, start_price as price 
                   From OpenAuction)) P
       Group By  P.itemID
   
   Select   Rstream(C.itemID, P.price)
   From     ClosedAuction [Now] C, CurrentPrice P
   Where    C.itemID = P.itemID
   

   
   Comments: We assume that the closing price in an auction is the price of the maximum bid, or the starting price of the auction in case there were no bids.
   
   
6. Average Closing Price Query: Monitor the average closing price across items in each category over the last hour.
   
   

   CurrentPrice: 
       Select    P.itemID, Max(P.price) as price
       From      ((Select itemID, bid_price as price 
                   From   Bid) Union All
                  (Select itemID, start_price as price 
                   From OpenAuction)) P
       Group By  P.itemID
   
   ClosingPriceStream:
       Select   Rstream(T.id as catID, P.price as price)
       From     ClosedAuction [Now] C, CurrentPrice P, 
                Item I, Category T    
       Where    C.itemID = P.itemID and C.itemID = I.id and I.categoryID = T.id 
   
   AvgPrice:
       Select   catID, Avg(price)
       From     ClosingPriceStream [Range 1 Hour]
       Group By catID 
   

   
   
   
7. Short Auctions Query: Report all auctions that closed within five hours of their opening.
   
   

   Select Rstream(OpenAuction.*)
   From   OpenAuction [Range 5 Hour] O, ClosedAuction [Now] C
   Where  O.itemID = C.itemID
   

   
   
   
8. Hot Item Query: Select the item(s) with the most bids in the past hour. Update the results every minute.
   
   

   HotItemStream: 
       Select Rstream(itemID)
       From   (Select   B1.itemID as itemID, Count(*) as num
               From     Bid [Range 60 Minute
                             Slide 1 Minute] B1
               Group By B1.itemID) 
       Where   num >= All (Select   Count(*)
                           From     Bid [Range 60 Minute
                                         Slide 1 Minute] B2
                           Group By B2.itemID)
   
   Select *
   From   HotItemStream [Range 1 Minute]
   

   
   
   
9. Average Selling Price By Seller Query: For each seller, maintain the average selling price over the last 10 items sold.
   
   

   CurrentPrice: 
       Select    P.itemID, Max(P.price) as price
       From      ((Select itemID, bid_price as price 
                   From   Bid) Union All
                  (Select itemID, start_price as price 
                   From OpenAuction)) P
       Group By  P.itemID
   
   ClosingPriceStream:
       Select   Rstream(O.sellerID as sellerID, P.price as price)
       From     ClosedAuction [Now] C, CurrentPrice P, 
                OpenAuction O
       Where    C.itemID = P.itemID and C.itemID = O.itemID
   
   AvgSellingPrice:
       Select   sellerID, Avg(price)
       From     ClosingPriceStream [Partition By sellerID Rows 10] 
       Group By sellerID 
   

   
   
   
10. Highest Bid Query: Every 10 minutes return the highest bid(s) in the recent 10 minutes.
    
    

    Select  Rstream(itemID, bid_price)
    From    Bid [Range 10 Minute
                 Slide 10 Minute]
    Where   bid_price = (Select  Max(bid_price)
                         From    Bid [Range 10 Minute]
                                      Slide 10 Minute]
    

    
    
    
11. Monitor New Users Query: Find people who put up something for sale within 12 hours of registering to use the auction service.
    
    

    NewPersonStream:
      Select Istream(P.id, P.name)
      From   Person P
    
    Select Distinct(P.id, P.name)
    From   Select Rstream(P.id, P.name)
           From   NewPersonStream [Range 12 Hour] P, OpenAuction A [Now]
           Where  P.id = A.sellerID