SQL: How To Query my customers who made a purchase after 6 months of registering with us?
I have a table that I can pull up that shows daily if a customer made a purchase or not and the values are 0 or 1 for the day with the list of customers.
Table = Customers_daily,
column = made_purchase = 1 or 0
Select date_utc, count(distinct customer_id), made_purchase
from customers_daily
If i wanted to analyze the customers who made a purchase only in their 6th month since reg_date where should i specify that?
this is the output im looking for
Im just trying to make sure on this date_utc we are pulling the customers who are in their 6th month since registration date
mysql sql where-clause metabase
asked Nov 20 '18 at 17:25
GusGus
635
add a comment |
I have a table that I can pull up that shows daily if a customer made a purchase or not and the values are 0 or 1 for the day with the list of customers.
Table = Customers_daily,
column = made_purchase = 1 or 0
Select date_utc, count(distinct customer_id), made_purchase
from customers_daily
If i wanted to analyze the customers who made a purchase only in their 6th month since reg_date where should i specify that?
this is the output im looking for
Im just trying to make sure on this date_utc we are pulling the customers who are in their 6th month since registration date
mysql sql where-clause metabase
asked Nov 20 '18 at 17:25
GusGus
635
3
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
You would need to use the registration date, so information about that field would be helpful. Also you saycustomers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.
– fqhv
Nov 20 '18 at 17:50
add a comment |
I have a table that I can pull up that shows daily if a customer made a purchase or not and the values are 0 or 1 for the day with the list of customers.
Table = Customers_daily,
column = made_purchase = 1 or 0
Select date_utc, count(distinct customer_id), made_purchase
from customers_daily
If i wanted to analyze the customers who made a purchase only in their 6th month since reg_date where should i specify that?
this is the output im looking for
Im just trying to make sure on this date_utc we are pulling the customers who are in their 6th month since registration date
mysql sql where-clause metabase
asked Nov 20 '18 at 17:25
GusGus
635
I have a table that I can pull up that shows daily if a customer made a purchase or not and the values are 0 or 1 for the day with the list of customers.
Table = Customers_daily,
column = made_purchase = 1 or 0
Select date_utc, count(distinct customer_id), made_purchase
from customers_daily
If i wanted to analyze the customers who made a purchase only in their 6th month since reg_date where should i specify that?
this is the output im looking for
Im just trying to make sure on this date_utc we are pulling the customers who are in their 6th month since registration date
mysql sql where-clause metabase
mysql sql where-clause metabase
asked Nov 20 '18 at 17:25
GusGus
635
asked Nov 20 '18 at 17:25
GusGus
635
edited Nov 20 '18 at 17:41
Gus
asked Nov 20 '18 at 17:25
GusGus
635
asked Nov 20 '18 at 17:25
GusGus
635
asked Nov 20 '18 at 17:25
GusGus
635
635
3
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
You would need to use the registration date, so information about that field would be helpful. Also you saycustomers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.
– fqhv
Nov 20 '18 at 17:50
add a comment |
3
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
You would need to use the registration date, so information about that field would be helpful. Also you saycustomers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.
– fqhv
Nov 20 '18 at 17:50
3
3
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
You would need to use the registration date, so information about that field would be helpful. Also you say
customers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.– fqhv
Nov 20 '18 at 17:50
You would need to use the registration date, so information about that field would be helpful. Also you say
customers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.– fqhv
Nov 20 '18 at 17:50
add a comment |
1 Answer
1
active
oldest
votes
http://sqlfiddle.com/#!9/b65b3fd/1
SELECT date_utc,
count(distinct customer_id),
MAX((date_utc >= reg_date + INTERVAL 5 MONTH) AND (date_utc <= reg_date + INTERVAL 6 MONTH))
FROM customers_daily
GROUP BY customer_id
The only thing I can't get is count(distinct customer_id) - it will always return 1, since I GROUP BY customer_id?
But if you want to GROUP BY date_utc - than what made_purchase meaning over many different customers?
Please provide sample of raw data and expected result.
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398345%2fsql-how-to-query-my-customers-who-made-a-purchase-after-6-months-of-registering%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
http://sqlfiddle.com/#!9/b65b3fd/1
SELECT date_utc,
count(distinct customer_id),
MAX((date_utc >= reg_date + INTERVAL 5 MONTH) AND (date_utc <= reg_date + INTERVAL 6 MONTH))
FROM customers_daily
GROUP BY customer_id
The only thing I can't get is count(distinct customer_id) - it will always return 1, since I GROUP BY customer_id?
But if you want to GROUP BY date_utc - than what made_purchase meaning over many different customers?
Please provide sample of raw data and expected result.
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
add a comment |
http://sqlfiddle.com/#!9/b65b3fd/1
SELECT date_utc,
count(distinct customer_id),
MAX((date_utc >= reg_date + INTERVAL 5 MONTH) AND (date_utc <= reg_date + INTERVAL 6 MONTH))
FROM customers_daily
GROUP BY customer_id
The only thing I can't get is count(distinct customer_id) - it will always return 1, since I GROUP BY customer_id?
But if you want to GROUP BY date_utc - than what made_purchase meaning over many different customers?
Please provide sample of raw data and expected result.
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
add a comment |
http://sqlfiddle.com/#!9/b65b3fd/1
SELECT date_utc,
count(distinct customer_id),
MAX((date_utc >= reg_date + INTERVAL 5 MONTH) AND (date_utc <= reg_date + INTERVAL 6 MONTH))
FROM customers_daily
GROUP BY customer_id
The only thing I can't get is count(distinct customer_id) - it will always return 1, since I GROUP BY customer_id?
But if you want to GROUP BY date_utc - than what made_purchase meaning over many different customers?
Please provide sample of raw data and expected result.
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
http://sqlfiddle.com/#!9/b65b3fd/1
SELECT date_utc,
count(distinct customer_id),
MAX((date_utc >= reg_date + INTERVAL 5 MONTH) AND (date_utc <= reg_date + INTERVAL 6 MONTH))
FROM customers_daily
GROUP BY customer_id
The only thing I can't get is count(distinct customer_id) - it will always return 1, since I GROUP BY customer_id?
But if you want to GROUP BY date_utc - than what made_purchase meaning over many different customers?
Please provide sample of raw data and expected result.
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
edited Nov 20 '18 at 18:00
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
answered Nov 20 '18 at 17:53
AlexAlex
14.5k11937
14.5k11937
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53398345%2fsql-how-to-query-my-customers-who-made-a-purchase-after-6-months-of-registering%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Please go through this link once: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 20 '18 at 17:26
Isn't there a customer id in the table?
– forpas
Nov 20 '18 at 17:27
yes there is a customer id in the table. I was actually thinking about doing a count(distinct customer_id)
– Gus
Nov 20 '18 at 17:34
You would need to use the registration date, so information about that field would be helpful. Also you say
customers who made a purchase only in their 6th month since reg_date, but this does not match the example output at all.. please provide a complete example.– fqhv
Nov 20 '18 at 17:50