mohan_mecherla
Reputation: 37
how to use nested functions for different tables in postgresql?
**table name:order_details table name:customer_details**
email | order_id email | customer_reg_date
------------ ---------- ----------- -----------------
[email protected] 201 [email protected] 2019-05-24
[email protected] 202 [email protected] 2019-07-15
[email protected] 203 [email protected] 2019-03-29
[email protected] 204
[email protected] 205
**Result**
email || average_weeks_per_order
------ -----------------------
[email protected] 9
1.count the number of order for each email
2.number of days between customer_reg_date and the current day then subtract with 7.
3.subtract s-2 with number of orders in s-1
Example:-
[email protected]
number of orders-2
(current_date - '2019-05-24')/7=18
18/2=9
ans:9
I mean,
(current_day-customer_reg_date/7)/number of order-This shoud be calculated for each customer.
I tried the following code but results are not efficient and getting more emails
QUERY:select distinct a.email,
(((current_date -b.created_on)/7)/(count(distinct(a.order_id))))as average_weeks_perorder
FROM ord_details a,cus_details b
where a.email not in(select email from cus_details where customer_groups='internal')
GROUP by a.email,b.created_on;
SCREENSHOT:
[enter image description here][1]
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
[email protected] 7
[email protected] 8
[email protected] 9
[email protected] 10
[email protected] 11
[email protected] 12
[email protected] 13
[email protected] 14
[email protected] 15
[email protected] 16
[email protected] 17
[email protected] 18
[email protected] 19
[email protected] 20
[email protected] 21
[email protected] 22
[email protected] 23
[email protected] 24
[email protected] 25
[email protected] 26
[email protected] 27
[email protected] 28
[email protected] 32
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 0
[email protected] 1
[email protected] 2
[email protected] 3
[email protected] 4
[email protected] 5
[email protected] 6
Upvotes: 1
Views: 50
Answers (1)
GMB
Reputation: 222582
If I followed you correctly, you can use an aggregate query to count the number of orders per customer, and apply the arithmetic as needed:
SELECT c.email, (current_date - c.customer_reg_date) / 7 / COUNT(*) res
FROM customer_details c
INNER JOIN order_details o ON c.email = o.email
GROUP BY c.email, c.customer_reg_date
| email | res |
| ------------ | --- |
| [email protected] | 5 |
| [email protected] | 26 |
| [email protected] | 9 |
Sample Data:
CREATE TABLE order_details(
email VARCHAR(12) NOT NULL
,order_id INTEGER NOT NULL
);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',201);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',202);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',203);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',204);
INSERT INTO order_details(email,order_id) VALUES ('[email protected]',205);
CREATE TABLE customer_details(
email VARCHAR(12) NOT NULL PRIMARY KEY
,customer_reg_date DATE NOT NULL
);
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-05-24');
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-07-15');
INSERT INTO customer_details(email,customer_reg_date) VALUES ('[email protected]','2019-03-29');
Upvotes: 1
Related Questions
- How do I create a nested function in PL/pgSQL?
- Nested SQL call
- calling a function in sql within nested loop
- Executing functions inside other functions
- How to reference arguments between nested and top-level function
- Using variables from parent in nested postgresql function?
- How to call table function from another function in PostgreSQL?
- Postgres nesting functions
- How to create a function that loops through another function in PostgreSQL?
- How to write multiple queries in a single function in postgresql