SQL Server: how to get record of last 6 weeks

Question

I have a table called Orders which looks like this:

Order_id         Integer    Ex: 10618834, 10618835
Customer_id      Integer    Ex: 231, 234
Order_Timestamp  Date       Ex: 10/25/2016 12:30:00 PM, 10/21/2016 2:32:31 PM
Order_status     Varchar    Ex: Success, Failure

I want to write the queries for these questions:

write a SQL query to get the week on week count of distinct users, count of successful orders for last 6 weeks.
write a SQL query to get the count of distinct users, who made their first successful order in April 2016 and made their 2nd successful order within 30 days from their first order date

It would be better if you provide [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) — Prashant Pimpale, Dec 19 '18 at 05:16
Yes, why not but without a *`valid`* sample data, expected output it is not possible — Prashant Pimpale, Dec 19 '18 at 05:25
Provide script to generate sample data, so anyone can try with the data — Prashant Pimpale, Dec 19 '18 at 05:25
for firts question output be like : WeekNo Users 51 104 50 52 49 70 48 100 47 78 46 51 — Suresh Eduru, Dec 19 '18 at 05:32
second question: count of Users -April 2016/1st order count of same users who made 2d orders with in 30 days afters their first order done success fully) 10 5 — Suresh Eduru, Dec 19 '18 at 05:34
Refer this:https://stackoverflow.com/questions/53372997/pivot-query-on-distinct-records to provide sample data and how to ask! — Prashant Pimpale, Dec 19 '18 at 05:44

score 0 · Accepted Answer · answered Dec 19 '18 at 06:30

Following query might be useful to achieve your first point.

SELECT DISTINCT DATEPART(week,Order_TimeStamp) AS [Week],count( DISTINCT Customer_id) AS Users,count(*) AS Orders FROM Orders WHERE Order_TimeStamp >=DATEADD(WEEK, -6, GETDATE()) AND dbo.Orders.order_status='success' GROUP BY DATEPART(week,Order_TimeStamp)

SQL Server: how to get record of last 6 weeks

1 Answers1