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):

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.
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.
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.

To get the last 4 weeks, use monday > DATE_SUB(NOW(), INTERVAL 4 WEEK). — Barmar
– Barmar, Commented Jan 25, 2024 at 20:35
You probably should do this as separate queries. You can merge the results with UNION to get the clients that meet any of the conditions. — Barmar
– Barmar, Commented Jan 25, 2024 at 20:38
Is that the kind of help you're looking for? It's not clear how much further we can go without just writing it for you. — Barmar
– Barmar, Commented Jan 25, 2024 at 20:38
@Barmar Thank you for reply. I need last active (4) weeks which can be in two months for example if the client orders from time to time. I need the query - even the standalone query and later can combine it with the existing one. — Dodo
– Dodo, Commented Jan 25, 2024 at 20:42
Assuming you are using atleast 10.2, study up on CTEs and windowing functions. — Rick James
– Rick James, Commented Jan 26, 2024 at 6:45

Dodo · Accepted Answer · 2024-01-29 09:21:18Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related