Querying sales, grouped by weeks

I'm trying to put together a query in SQLLite for my app and wanted to ask for a little help please.

The query I want to write:

"I'm a user with UserID x. For my personal work week, which can start on any day D that I want (Sunday to Saturday, Monday to Sunday, etc), I'd like to see which work week was my worst, which was my best, in terms of sales.

To keep it simple "worst" and "best" simply means the highest and lowest sales summed for that week.

If everyone's work week started on Sunday, this is easy, but this is not the case. I have to overcome a SQL challenge of grouping all of the rows in the database table not just by week, but a custom week (users define which day starts and ends the week).

As an example, if my work week starts on Sunday, then this past week, the week of May 28th is a Sunday and is the beginning of my work week (and it ends this Saturday June 3rd). I would follow this pattern for all records in the table.

However, a different user could have their work week start on Monday, May 29th, and end on Friday, June 2nd.

So this means for user 1, I'd want to group his rows from the starting day of Sunday, and the ending day of Saturday (and then aggregate them all, and take the first and last records for sales).

For user 2, however, I'd want to group his records in the date ranges of Monday to Sunday

Here is where I'm at so far. I'm close I think.

(Note that I store the date as a unix timestamp in milliseconds, hence the division by 1000 and the unixepoch part). The +d part is actually an integer based on the start of the day, but I haven't figured out what that number should really be. Just when I think I get it, it fails for someone else's day.

SELECT 'Date', SUM(Amount) 'Amount'  
FROM Sales WHERE UserID = x
GROUP BY CAST(( julianday((datetime(CreationDate / 1000, 'unixepoch', 'localtime')) + d) / 7 ) AS INT)

Does anyone think they could give me a hand? :)

Thanks so much!

EDIT

Thank you so much for your help!

For the +d question (what should the value 'd' be to offset)? Here is what I found after testing, and this works as far as I can tell. I understand Sqllite uses 0 as Sunday, 1 as Monday, etc, and I understand we were grouping and dividing by 7 (for 7 days in the week), but any idea why these would be the right values for 'd' as the offset? It seems to be working now. I see the pattern goes 2,1,0,6,5,4,3 but kinda strange order to go in eh?

if (day == Sunday) //if your work week starts on Sunday, d=2
     return 2
else if (day == Monday)
     return 1
else if (day == Tuesday)
     return 0
else if (day == Wednesday) 
     return 6
else if (day == Thursday) 
     return 5
else if (day == Friday) 
     return 4
else if (day == Saturday) 
     return 3

Solution

You are very close.

You are adding d to the datetime. I don't know whether this actually adds days. I could not find out what happens if you add an integer to a datetime in SQLite. To play it save, add the day to the julian day instead. You don't have to first get a datetime and from this the julian day by the way, you can do that in one step:
```
julianday(CreationDate / 1000, 'unixepoch', 'localtime') + d
```
This is the only real flaw I see in your query.
the Julian day is a fractional number such as 2457907.5. When you invoke a division with / on it, you get a fractional result. I see that you convert this result to INT, but I would suggest to convert to INT first and only then divide which would make this an integer division implicitly.

cast(julianday(CreationDate / 1000, 'unixepoch', 'localtime') + d as int) / 7

This is just for readability; I get a day number (2457907 rather than some decimal 2457907.5) and integer-divide by 7 (e.g. 2457907 / 7 = 351129).

The whole query:

SELECT 
  MIN(DATE(CreationDate / 1000, 'unixepoch', 'localtime')) AS from_date, 
  MAX(DATE(CreationDate / 1000, 'unixepoch', 'localtime')) AS till_date, 
  SUM(Amount) AS total
FROM Sales 
WHERE UserID = x
GROUP BY CAST(JULIANDAY(CreationDate / 1000, 'unixepoch', 'localtime') + d as INT) / 7
ORDER BY SUM(Amount);

from_date and till_date don't always represent the full seven days though, but only the worked days (e.g. in a week from Sunday to Saturday, but worked only Monday, Wednesday and Friday, it would show the dates for Monday and Friday). It would take slightly more work to show the real week. (I better don't try this now, for it's so easy to be one day off, when not being able to try the query.)

EDIT: Here is my try on the start and end days of the weeks. When we invoke DATE on a floating point value, this value is considered a Julian day. (Maybe it would work with the integer, too, I can not be sure from the documentation.)

SELECT 
  DATE(CAST(CAST(JULIANDAY(CreationDate / 1000, 'unixepoch', 'localtime') + d as INT) / 7 as REAL)) AS from_date,
  DATE(CAST(CAST(JULIANDAY(CreationDate / 1000, 'unixepoch', 'localtime') + d as INT) / 7 as REAL), '+6 day') AS till_date,
  MIN(DATE(CreationDate / 1000, 'unixepoch', 'localtime')) AS first_working_day, 
  MAX(DATE(CreationDate / 1000, 'unixepoch', 'localtime')) AS last_working_day, 
  SUM(Amount) AS total
FROM Sales 
WHERE UserID = x
GROUP BY CAST(JULIANDAY(CreationDate / 1000, 'unixepoch', 'localtime') + d as INT) / 7
ORDER BY SUM(Amount);