SELECT DISTINCT ID,
FIRST_VALUE( ProductID ) OVER ( PARTITION BY ID ORDER BY LineID )
FROM SalesOrderItems
ORDER BY ID;

SELECT s.ProductID AS prod_id, o.SalesRepresentative AS sales_rep,
    SUM( s.Quantity * p.UnitPrice ) AS total_sales,
    100 * total_sales / ( FIRST_VALUE( SUM( s.Quantity * p.UnitPrice ) )
                          OVER Sales_Window ) AS total_sales_percentage
  FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
  GROUP BY o.SalesRepresentative, s.ProductID    
    WINDOW Sales_Window AS ( PARTITION BY s.ProductID 
                             ORDER BY SUM( s.Quantity * p.UnitPrice ) DESC )
  ORDER BY s.ProductID;

SELECT v.OrderDate, v.SalesRepresentative AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity * 
                              p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
WHERE v.sales_ranking = 1
AND v.OrderDate BETWEEN '2001-04-01' AND '2001-04-07'
ORDER BY v.OrderDate;

SELECT d.dense_order_date,
                   LAST_VALUE( v.SalesRepresentative IGNORE NULLS )
                       OVER ( ORDER BY d.dense_order_date )
                       AS rep_of_the_day
FROM ( SELECT o.SalesRepresentative, o.OrderDate,
              RANK() OVER ( PARTITION BY o.OrderDate
                            ORDER BY SUM( s.Quantity *
                             p.UnitPrice ) DESC ) AS sales_ranking
       FROM SalesOrders o KEY JOIN SalesOrderItems s KEY JOIN Products p
       GROUP BY o.SalesRepresentative, o.OrderDate ) AS v
RIGHT OUTER JOIN ( SELECT DATEADD( day, row_num, '2001-04-01' )
                        AS dense_order_date
                   FROM sa_rowgenerator( 0, 6 )) AS d 
ON v.OrderDate = d.dense_order_date AND sales_ranking = 1
ORDER BY d.dense_order_date;