Determine customers that have spent money at the company for the first time, each month (mySQL)

write a query to display the names of customers who have ordered more than 2 times.
write an sql command that will find any customers who have not placed orders
write a query to find the name and numbers of all salesmen who had more than one customer.
consider the following vendor table and write the queries write a query to display all records
a department store maintains data on customers products and purchase records in three tables
sql count number of orders per customer
sql query to retrieve data from database
count in sql

I have a table below and I am trying to determine the number of users that have spent money at the company for the first time each month.

What I would like is to have a resulting table with new users, month and year as columns.

Before people downvote this post, I have looked through various posts already and can't seem to find a similar way to solve this issue. The code that I have included below is based on what I've managed to piece together from relevant posts.

This is the original table:

+---------------------+-------------+-----------------+
| datetime            | customer_id | amount          |
+---------------------+-------------+-----------------+
| 2018-03-01 03:00:00 | 3786        | 14              |
+---------------------+-------------+-----------------+
| 2018-03-02 17:00:00 | 5678        | 25              |
+---------------------+-------------+-----------------+
| 2018-08-17 19:00:00 | 5267        | 45              |
+---------------------+-------------+-----------------+
| 2018-08-25 08:00:00 | 3456        | 78              |
+---------------------+-------------+-----------------+
| 2018-08-25 17:00:00 | 3456        | 25              |
+---------------------+-------------+-----------------+
| 2019-05-25 14:00:00 | 3456        | 15              |
+---------------------+-------------+-----------------+
| 2019-07-02 14:00:00 | 88889       | 45              |
+---------------------+-------------+-----------------+
| 2019-08-25 08:00:00 | 1234        | 88              |
+---------------------+-------------+-----------------+
| 2019-08-30 09:31:00 | 1234        | 30              |
+---------------------+-------------+-----------------+
| 2019-08-30 12:00:00 | 9876        | 55              |
+---------------------+-------------+-----------------+
| 2019-09-01 13:00:00 | 88889       | 23              |
+---------------------+-------------+-----------------+

This is the CREATE statement:

CREATE TABLE IF NOT EXISTS `spend` ( `datetime` datetime NOT NULL, `customer_id` int(11) NOT NULL, `amount` int(11) NOT NULL, PRIMARY KEY (`datetime`)) DEFAULT CHARSET=utf8mb4;
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-01 03:00:00', 3786, 14);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-03-02 17:00:00', 5678, 25);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-17 19:00:00', 5267, 45);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 08:00:00', 3456, 78);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2018-08-25 17:00:00', 3456, 25);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-05-25 14:00:00', 3456, 15);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-07-02 14:00:00', 88889, 45);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-25 08:00:00', 1234, 88);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 09:31:00', 1234, 30);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-08-30 12:00:00', 9876, 55);
INSERT INTO `spend` (`datetime`, `customer_id`, `amount`) VALUES ('2019-09-01 13:00:00', 88889, 23);

This is the code that I have come up with:

SELECT S.datetime, S.customer_id, S.amount 
FROM spend S
INNER JOIN
    (SELECT customer_id, MIN(datetime) AS first_occurence
    FROM spend
    GROUP BY customer_id) X
ON S.customer_id = X.customer_id AND S.datetime = X.first_occurence

This is the resulting table:

+------------------+-------------+-------+
| datetime         | customer_id |amount |
+------------------+-------------+-------+
| 01/03/2018 03:00 | 3786        | 14    |
+------------------+-------------+-------+
| 02/03/2018 17:00 | 5678        | 25    |
+------------------+-------------+-------+
| 17/08/2018 19:00 | 5267        | 45    |
+------------------+-------------+-------+
| 25/08/2018 08:00 | 3456        | 78    |
+------------------+-------------+-------+
| 02/07/2019 14:00 | 88889       | 45    |
+------------------+-------------+-------+
| 25/08/2019 08:00 | 1234        | 88    |
+------------------+-------------+-------+
| 30/08/2019 12:00 | 9876        | 55    |
+------------------+-------------+-------+

This is what an example of what the table should look like:

+-----------+-------+------+
| new_users | month | year |
+-----------+-------+------+
| 2         | 3     | 2018 |
+-----------+-------+------+
| 3         | 8     | 2018 |
+-----------+-------+------+
| 1         | 5     | 2019 |
+-----------+-------+------+
| 1         | 7     | 2019 |
+-----------+-------+------+
| 3         | 8     | 2019 |
+-----------+-------+------+
| 1         | 9     | 2019 |
+-----------+-------+------+

You started correctly. Now use that as a subquery to get the count by month.

SELECT COUNT(*) AS new_users, MONTH(datetime) AS month, YEAR(datetime) AS year
FROM (
    SELECT S.datetime, S.customer_id, S.amount 
    FROM spend S
    INNER JOIN
        (SELECT customer_id, MIN(datetime) AS first_occurence
        FROM spend
        GROUP BY customer_id) X
    ON S.customer_id = X.customer_id AND S.datetime = X.first_occurence
) AS x
GROUP BY month, year
ORDER BY year, month

Actually, you don't even need the join in the subquery, since you're not using the amount from the first purchase in the final result.

SELECT COUNT(*) AS new_users, MONTH(datetime) AS month, YEAR(datetime) AS year
FROM (
    SELECT customer_id, MIN(datetime) AS datetime
    FROM spend
    GROUP BY customer_id
) AS x
GROUP BY month, year
ORDER BY year, month

CIO, It didn't even have a customer yet, but the product seemed ideal for Epsilon's needs. By being a first customer, Epsilon carried a lot of weight with Netezza, which But unlike a large company that might have simply added Epsilon's requests "We could have spent any amount of money we needed," noting that the data  Customer lifetime value (or life-time value (LTV), is the average amount of money your customers will spend on your business over the entire life of your relationship. For instance, if a customer continues to buy products or services from your business for 10 years and spends $10 per year, his or her customer lifetime value is $100, minus any

You dont need two levels deep subquery. You can simply find the first time a customer spent money using MIN() and then simply extract YEAR() and MONTH() out of that minimum datetime value, to count number of users:

SELECT 
  YEAR(min_dt) y,
  MONTH(min_dt) m,
  COUNT(*) AS new_customers 
FROM 
(
  SELECT customer_id, MIN(datetime) AS min_dt 
  FROM spend 
  GROUP BY customer_id 
) t
GROUP BY y, m

Result

| y    | m   | new_customers |
| ---- | --- | ------------- |
| 2018 | 3   | 2             |
| 2018 | 8   | 2             |
| 2019 | 7   | 1             |
| 2019 | 8   | 2             |

View on DB Fiddle

Expert PHP and MySQL: Application Design and Development, I've already explained why the first three tasks are so critical, and I'll have It's too inefficient to decide matters by consensus, and it runs the risk of losing track of By the time I was brought in to manage engineering, they had already spent a another $250K or so of VC money and finish the system in about six months. If I  In order to display names of customers who have purchased all the DVD, this is the query that I have tried out: SELECT Customer_Name FROM Customer WHERE EXISTS (SELECT Customer_Name,DVD_Name FROM Customer,DVD,DVD_Purchase WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

With ROW_NUMBER() window function:

select 
  count(*) new_users,
  month(t.datetime) month,
  year(t.datetime) year
from (
  select *,
    row_number() over (partition by customer_id order by datetime) rn
  from spend
) t
where t.rn = 1
group by year, month
order by year, month

See the demo for your sample data. Results:

| new_users | month | year |
| --------- | ----- | ---- |
| 2         | 3     | 2018 |
| 2         | 8     | 2018 |
| 1         | 7     | 2019 |
| 2         | 8     | 2019 |

QUERYING THE DATABASE: QUERIES and VIEWS, Note: The table names need not be repeated unless the same column names exist in (Not A Good Thing - the query may run for a very long time and produce a huge, Access, MySQL, and Oracle all use similar syntax, with more join types and Modify the Orders query to show only customers from Oregon and list each​  1. Determine the business's Revenue 2. Calculate the Cost of Goods Sold 3. Determine the Gross Profit 4. Calculate Operating Expenses 5. Determine the Pre-Tax Profit 6. Determine the Net Profit or Loss

You could also do

select   
count(*) new_users,
month(datetime) month,
year(datetime) year
from spend
where datetime in (select min(datetime) from spend group by customer_id)
group by year, month
order by year, month;

Quick Guide: Calculate Cohort Retention Analysis with SQL, Define Retention: If first-time user A goes to the store on Week 1, and returns a user (despite all the marketing money you spent to lure him to your store). (​user_id, month_number): user X has activity in month number X WITH based businessman whose family bought and fixed distressed companies. But deciding on your company IT spend requires more than just picking numbers that sound good! "Businesses are confused and uncertain as to what an appropriate IT budget is, and they don't have a clear vision how these funds should be spent" ( American Structurepoint ).

Chapter 16 SQL Data Manipulation Language – Database Design , SELECT – to query data in the database; INSERT – to insert data into a table; UPDATE If a clause has several parts, they should appear on separate lines and be These first two examples illustrate how to limit record selection with the and getdate function determine how much time has elapsed in months since the  In addition to service received, the return policy of an organization is another gauge customers use to determine where they will spend their time and money. T/F - An organization should periodically conduct an inspection of its systems and practices in order to better serve customers and to be competitive in a global service economy.

[SQL Basic] Let's find customers that have rented movies priced , Find customers in store #1 that spent less than $2.99 on individual rentals, but have spent a total For a subquery to work correctly, we need to first make sure the inside query works on its own. It's time to write the outside query. Sqlgate · Sql Tutorial · MySQL · Database · Sql Basic Just $5/month. Few small businesses make much profit their first year - if they make any profit at all. Even with a low start-up cost business, you will eventually need to invest money into your business in order to grow it by expanding products, services, moving into new geographic territories, or hiring others to help you.

Handy MySQL Date functions with examples., Date information tracking and storage is integral to companies, The MySQL Date and Time Functions documentation has a wealth of can determine how many birthdays we need to attend for a given month. page to see blog post/​technical writing I have completed for clients. Write the first response  Calculate the variable costs of sales for each month based on sales for the month. For example, if your estimated sales for a month are 2,500 units and your variable costs are $5.50 per unit, total variable costs for the month would be $13,750. Add monthly variable costs to monthly fixed costs to get total monthly costs (expenses).

Comments
  • Thanks for helping! That makes much more sense now and it works perfectly :D
  • Thanks! This gives me what I was looking for :)