Untitled

CREATE VIEW HRM1746EV_User_Spending_Details AS
SELECT 
    u.Firstname,
    UPPER(u.Lastname) AS Lastname,
    LEFT(u.Email, CHARINDEX('.', u.Email) - 1) + '@' + SUBSTRING(u.Email, CHARINDEX('@', u.Email) + 1, CHARINDEX('.', u.Email) - CHARINDEX('@', u.Email) - 1) AS Email_Without_Domain,
    s.Salesorderid,
    s.Userid,
    s.Orderqty,
    s.Productid,
    s.TotalAmount,
    COUNT(s.Productid) OVER(PARTITION BY s.Userid, CONVERT(DATE, s.OrderDate)) AS Products_Purchased_Per_Day
FROM 
    HRM1746EV_Sales_2 s
JOIN 
    HRM1746EV_Users u ON s.Userid = u.Userid
JOIN 
    HRM1746EV_Product_Details pd ON s.Productid = pd.Productid
WHERE 
    LEN(s.Userid) BETWEEN 3 AND 4
    AND s.Orderqty > 20
    AND s.TotalAmount > 2000;

CREATE NONCLUSTERED INDEX idxSalesUserid 
ON HRM1746EV_Sales_2 (Userid);