Loyalty Group Order Milestones :Determine the Earliest Order Date and First Customer for Each Loyalty Tier Intermediate

          with loyalty as
  (select c.loyaltystatus,
          o.customerid,
          o.orderdate,
          first_value(o.orderdate) over (partition by c.loyaltystatus
                                         order by o.orderdate asc) as firstorderdate
   from orders o
   join customers c on o.customerid = c.customerid)
select loyaltystatus,
       customerid,
       firstorderdate
from loyalty
where orderdate = firstorderdate;