Untitled
unknown
plain_text
a year ago
990 B
6
Indexable
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;
Editor is loading...
Leave a Comment