BigQuery Order By one financial year(52 weeks)

I have this dataset right now.

Date	Sales	Group
2022-11-02	xxxxxxxx	A
2022-11-03	xxxxxx	A
2022-11-03	xxxxxx	B
2021-11-03	xxxxxx	A
2021-11-04	xxxxxx	B
2021-11-04	xxxxxx	A

I want to order my data as this, where it will order the date by one year

Date	Sales	Group
2022-11-02	xxxxxxxx	A
2021-11-03	xxxxxx	A
2022-11-03	xxxxxx	A
2021-11-04	xxxxxx	A
2022-11-03	xxxxxx	B
2021-11-04	xxxxxx	B

(because they have 52 weeks of interval)

Is there a possible way to do it?

I want to avoid join! Sorry just to make it clear, I need to make sure that the the first row['date'] and second row['date'] has exactly 52 weeks of interval i.e. date_sub(second_row['date'],interval 52 week) == first row['date']

Really sorry for the confusing

Solution

Consider below instead of previous answer.

WITH sample_data AS (
    SELECT DATE '2022-11-02' Date, 'xxxxxxxx' Sales, 'A' `Group` UNION ALL
    SELECT '2022-11-03' Date, 'xxxxxx' Sales, 'A' `Group` UNION ALL
    SELECT '2022-11-03' Date, 'xxxxxx' Sales, 'B' `Group` UNION ALL
    SELECT '2021-11-03' Date, 'xxxxxx' Sales, 'A' `Group` UNION ALL
    SELECT '2021-11-04' Date, 'xxxxxx' Sales, 'B' `Group` UNION ALL
    SELECT '2021-11-04' Date, 'xxxxxx' Sales, 'A' `Group`
)
SELECT *
  FROM sample_data
 ORDER BY `Group`,
       EXTRACT(WEEK FROM Date) || EXTRACT(DAYOFWEEK FROM Date),
       EXTRACT(YEAR FROM Date) DESC;

BigQuery Order By one financial year(52 weeks)

Query results: