Untitled

CREATE VIEW YourUsername_View AS
SELECT 
    CONCAT(LEFT(u.username, CHARINDEX(' ', u.username)), UPPER(RIGHT(u.username, LEN(u.username) - CHARINDEX(' ', u.username)))) AS UserName,
    LEFT(u.emailaddress, CHARINDEX('@', u.emailaddress) - 1) + '@' + SUBSTRING(u.emailaddress, CHARINDEX('@', u.emailaddress) + 1, CHARINDEX('.', u.emailaddress) - CHARINDEX('@', u.emailaddress) - 1) AS Email,
    s.Salesorderid,
    s.user_id,
    s.Orderqty,
    s.ProductID,
    s.Orderqty * p.Listprice AS total_amount,
    COUNT(s.ProductID) OVER(PARTITION BY s.user_id, CONVERT(date, s.orderDate)) AS products_purchased_per_day
FROM 
    YourUsername_product_details p
JOIN 
    YourUsername_Sales_2 s ON p.ProductID = s.ProductID
JOIN 
    YourUsername_Users u ON s.user_id = u.User_id
WHERE 
    LEN(u.User_id) BETWEEN 3 AND 4
    AND s.Orderqty > 20
GROUP BY 
    u.username,
    u.emailaddress,
    s.Salesorderid,
    s.user_id,
    s.Orderqty,
    s.ProductID,
    p.Listprice,
    s.orderDate;