Using GROUP BY and PARTITION BY together-CodePudding

Here is my first query which returns the result in the image below

I get the "Restaurant Name" , "Customer Name", "Count of orders", & "date" from 3 tables namely "Orders", "Customers" and "Restaurants". Then I group it by the restaurant name, customer name, and date.

SELECT R.name AS name_of_r, C.name AS name_of_c, COUNT(O.id) AS Orders, date,
FROM orders O
INNER JOIN restaurants R ON R.id = O.restaurant_id AND R.country = O.country 
INNER JOIN customers C ON C.id = O.customer_id AND C.country = O.country
GROUP BY R.name, C.name, date

name_of_r       |   name_of_c    |       Orders       |       date
Howdy           | John Almagro   |          1         |     2021-01-07
Howdy           | John Almagro   |          1         |     2021-07-01
Howdy           | Luke Pablo     |          1         |     2021-01-01
Howdy           | Steven Smith   |          1         |     2021-09-01
JFC             | Monty Tron     |          1         |     2021-01-01
JFC             | Steven Smith   |          1         |     2021-05-04
JFC             | Steven Smith   |          1         |     2021-06-01
KFC             | Luke Pablo     |          1         |     2021-01-09
KFC             | Michael Wren   |          1         |     2021-01-01
KFC             | Monty Tron     |          1         |     2021-04-01
KFC             | Steven Smith   |          1         |     2021-01-01
KFC             | Steven Smith   |          1         |     2021-03-01
KFC             | Steven Smith   |          1         |     2021-08-01
Kitchen Cuisine | Luke Pablo     |          1         |     2021-01-05
Kitchen Cuisine | Luke Pablo     |          1         |     2021-04-01
Kitchen Cuisine | Steven Smith   |          1         |     2021-03-01
Kitchen Cuisine | Steven Smith   |          1         |     2021-06-01
McDonald's      | Arthur Chen    |          1         |     2021-01-01
McDonald's      | Arthur Chen    |          1         |     2021-03-02
McDonald's      | Arthur Chen    |          1         |     2021-05-03
McDonald's      | Arthur Chen    |          1         |     2021-07-01
McDonald's      | Arthur Chen    |          1         |     2021-08-01
McDonald's      | Arthur Chen    |          1         |     2021-09-01

Now, what I want to do is partition by "name_of_r" (RESTAURANT NAME) ORDER by "date" and give a row number to each so then I can get the 3rd value of each window.

Actually, this is my sub-query from which I want the customer's name who placed the 3rd order at each restaurant.

I tried doing this adding row_number() and partition by but it didn't work and gave me a syntax error

SELECT R.name AS name_of_r, C.name AS name_of_c, COUNT(O.id), date,
ROW_NUMBER() OVER(PARTITION BY R.name ORDER BY date) AS row_num
FROM orders O
INNER JOIN restaurants R ON R.id = O.restaurant_id AND R.country = O.country
INNER JOIN customers C ON C.id = O.customer_id AND C.country = O.country
GROUP BY R.name, C.name, date

What I want have as a final ouput is the name of the customer who placed the 3rd order at each restaurant. Like the table below:

name_of_restaurant       |  name_of_customer_who_placed_the_3rd_order
Howdy                    |      Luke Pablo
JFC                      |      Steven Smith
KFC                      |      Monty Tron
Kitchen Cuisine          |      Steven Smith
McDonald's               |      Arthur Chen

I know this becuase I ordered by my first table with date

CodePudding user response：

i think you need a HAVING COUNT

SELECT
    R.name AS name_of_r,
    C.name AS name_of_c,
    COUNT(O.id),
    date
  FROM 
    orders O
  INNER JOIN restaurants R ON R.id = O.restaurant_id AND R.country = O.country
  INNER JOIN customers C   ON C.id = O.customer_id   AND C.country = O.country
  GROUP BY R.name, C.name, date
  HAVING COUNT(O.id) >= 3
;

PS: But it will work for those who placed the 3rd order on the same day. Otherwise, date must be excluded from the grouping.

UPDATE: added a request for the selection of every third client in the restaurant.

SELECT name_of_r, name_of_c, date
  FROM (
    SELECT
        R.name AS name_of_r,
        C.name AS name_of_c,
        date,
        ROW_NUMBER() OVER (PARTITION BY R.name ORDER BY date) AS nc
      FROM 
        orders O
      INNER JOIN restaurants R ON R.id = O.restaurant_id AND R.country = O.country
      INNER JOIN customers C   ON C.id = O.customer_id   AND C.country = O.country
  ) t
  WHERE t.nc = 3
;

See the ROW_NUMBER Function