MariaDB query to extend with subquery based on last X records from joining table

Question

Tables clienta and orders contain client information and order information. Table weekly_analyses contains aggregated data for each client for the current week. A script takes all client orders at the end of the week, aggregates information, and creates a record in table weekly_analyses.

weekly_analyses:

• Monday is the start of the week
• Client_id is the client
• Brand - there are different brands from which client can order
• Store - there are different stores from which client can order
• orders - count of made orders for selected week, brand and store
• discount - the amount of discount of made orders for selected week, brand and store
• amount - total amount for made orders for selected week, brand and store
• some other parameters.

I'm using MariaDB. Fiddle: https://sqlfiddle.com/mariadb/online-compiler?id=8df9ba56-6774-4bc2-8ce4-6519ca6abc08

select {} from clients 
left join {...} 
where 
condition1 = 1
and (
   field1 = 1 OR field2 = 2
)
and (
   field3 = 3 OR field4 = 4
)
etc...

I need to add the following conditions to that query (using weekly_analyses table):

1. Find clients that have total count of orders for given period of time defined by user. Example: Give me clients that have 10 orders from 1st Dec 2023 to 15th Jan 2024 for specific brand and store.

2. Find clients that have discount >= X for specific brand and store. The tricky part: For the last 4 active weeks, which means the last four records for that user for selected brand and store. Active weeks are not calendar weeks, but weeks where the client has records.

3. Find clients that have amount >= X for specific brand and store based on last 4 active weeks. And so on.

How do I build a query that includes the existing one and to does the job for points 1 and 2, 3?

I do not know how to implement taking the last four weeks for each customer on join/select for subquery.

Answer 1

After some research, I built the query that I needed. I'm posting it here if someone needs something similar to my case:

SELECT
    client_id
        FROM
            (
                SELECT
                    client_id,
                    SUM(orders) AS total_orders
                FROM
                    (
                        SELECT
                            orders,
                            client_id,
                            monday,
                            `brand`,
                            `store`,
                            ROW_NUMBER() OVER (
                                PARTITION BY
                                    client_id, brand, chain
                                ORDER BY
                                    monday DESC
                            ) AS row_num
                        FROM
                            `weekly_analyses`
                        WHERE
                            `brand` IN ('Mine', 'Public')
                            AND `store` IN ('one', 'two')

                    ) AS `tempTable`
                WHERE
                    `row_num` <= 4
                GROUP BY
                    `client_id`
            ) AS `lastFourRecordsForEachBrandStore`
        WHERE
            `total_orders` > 5

In few words:

• I was needed to find in a table for each client last 4 records for each brand and store ordered by monday DESC column.
• Result of the query (clients.id) was used in other query that selects clients.