Related questions

count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to firsttime)

2022-05-07 03:10 KT.Thompson imported from Stackoverflow

Here is the question:count how many people who buy the same item at second time and how many people who did not buy the second item at second time (compare to buying_order = 1)

We have a table:

id item date buying_order
1 1 19990101 1
1 1 19990102 2
2 1 19990102 1
2 2 19990101 2
2 3 19990103 1

To be more clear, if id 1 buy item 1 on 19990101, that is the first purchased item so the buying_order is 1. Our concern is know how many people buy a specific item at their first time, and how many people re-purchase that specific item at the second time.

Here are the logic I have tried, but not work

SELECT a.id FROM (SELECT id FROM table WHERE buying_order = 1) AS a 
LEFT JOIN table AS b ON a.id = b.id 
WHERE b.buying_order=2 and a.item = b.item

Expected result:

item first_purchase_customer second_purchase second_buy_other_item_count
1 2 1 1

For item 1, there are two first purchase at order 1, and there is only one customer buy item 1 at order 2.

NOTE: The order can be higher than 2, such that order = 3,4,..., but we only care the people who buy or not buy the same item at their second purchase.

1 answer

  • answered 2022-05-07 06:15 Andrew

    Its not a hugely clear question. I would also raises questions why you even have the buying_order column, as it really doesn't seem to add value, you already have a row per user and item and also a date!

    You could simply do this to count the orders, again ignoring the buying_order column altogether

    CREATE TABLE #MyTable (
    UserId INT NOT NULL, 
    ItemId INT NOT NULL, 
    [Date] DATE NOT NULL, 
    BuyingOrder INT NOT NULL
);

INSERT INTO #MyTable(UserId, ItemId, [Date], BuyingOrder)
VALUES
(1, 1, '19990101', 1),
(1, 1, '19990102', 2),
(2, 1, '19990102', 1),
(2, 2, '19990101', 2),
(2, 3, '19990103', 1);

GO

SELECT UserId, ItemId, COUNT(*) NumberOfTimesBought
FROM #MyTable
GROUP BY UserId, ItemId

    enter image description here

    I was also thinking you could use the ROW_NUMBER solution as follows, which will just give you the items that have been bought more than once:

    WITH T AS (
    SELECT 
    UserId, 
    ItemId,
    ROW_NUMBER() OVER(PARTITION BY UserId, ItemId ORDER BY [Date] DESC) AS RowNumber
    FROM
    #MyTable
)

SELECT UserId, ItemId
FROM T
WHERE RowNumber > 1

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum

See also questions close to this topic

  • Why does this Sql query shows the possible changes but does not implement it?

    So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

    SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

    Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.

  • how to get weeklytotal and yesterday record in mysql in one table

    Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

    Table name-dailydata-

    Sample data

    employee_id date total
    20 2022-04-25 10
    20 2022-04-26 20
    20 2022-04-27 20
    20 2022-04-28 10
    20 2022-04-29 20
    20 2022-04-30 30
    20 2022-04-31 40
    20 2022-05-01 50
    40 2022-04-26 20

    expected output

    employee_id weekly_total yesterday_record
    20 200 40
    40 20 0

    mysql query to get weekly data

    select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  • Join to another table only matching specific records

    I have a table of ports:

    drop table if exists ports;
create table ports(id int, name char(20));

insert into ports (id, name ) values
  (1, 'Port hedland'), 
  (2, 'Kwinana');

    And a table of tariffs connected to those ports:

    drop table if exists tariffs;
create table tariffs(id int, portId int, price decimal(12,2), expiry bigint(11));

insert into tariffs (id, portId, price, expiry ) values
    (1, 2, 11.00, 1648408400), 
    (2, 2, 12.00, 1648508400), 
    (3, 2, 13.00, 1648594800), 
    (4, 2, 14.00, 1651273200),
    (5, 2, 15.00, 2250000000 );

insert into tariffs (id, portId, price, expiry ) values
    (1, 1, 21.00, 1648408400), 
    (2, 1, 22.00, 1648508400), 
    (3, 1, 23.00, 1648594800), 
    (4, 1, 24.00, 1651273200),
    (5, 1, 25.00, 2250000000 );

    Each tariff has an expiry.

    I can easily make a query to figure out the right tariff for as specific date for each port. For example at timestamp 1648594700 the right tariff is:

    SELECT * FROM tariffs 
WHERE 1648594700 < expiry AND portId = 2
ORDER BY expiry
LIMIT 1

    Result:

    id  portId  price   expiry
3   2   13.00   1648594800

    However, in my application I want to be able to pull in the right tariff starting from the ports record.

    For one record, I can do this:

    SELECT * FROM ports 
LEFT JOIN tariffs on tariffs.portId = ports.id 
WHERE 1648594700 < tariffs.expiry AND ports.id = 2
LIMIT 1

    Result:

    id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800

    This feels a little 'dirty', especially because I am doing a lookup on a record, and then forcing only one result using LIMIT. But, OK.

    What I cannot do, and can't work out how to do, is a query that will return a list of ports, and each port having a price field that matches the constraint above (that is, the record with the highest expiry compared to 1648594700 for each port).

    This obviously won't work:

    SELECT * FROM ports 
left join tariffs on tariffs.portId = ports.id
where 1648594700 < tariffs.expiry

    Since the result of the query, testing with timestamp 1648594700, would be:

    id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
2   Kwinana 4   2   14.00   1651273200
2   Kwinana 5   2   15.00   2250000000
1   Port he 3   1   23.00   1648594800
1   Port he 4   1   24.00   1651273200
1   Port he 5   1   25.00   2250000000

    Instead, the result for all ports (before further filtering) should be:

    id  name    id  portId  price   expiry
2   Kwinana 3   2   13.00   1648594800
1   Port he 3   1   23.00   1648594800

    Is there a clean, non-hacky way to have such a result? As an added constraint, is this possible for this to be done in ONE query, without temp tables etc.?

  • Procedure to display the Employees of a specific Department

    Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

    Name of the argument as Input : DeptNo

    Write the code to create the procedure

    Schema

    I wrote the Query like this but i didnt get expected output

    CREATE  PROCEDURE Employeesdept(@Deptno varchar)
AS
Begin
SELECT lastname as name
from employee
where workdept= 'D21'
end

    Expected output:

    Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------

  • Tables won't appear as selectable object after creating it 
    SELECT *
FROM EmployeeDemographics


Msg 208, Level 16, State 1, Line 2

Invalid object name 'EmployeeDemographics'.

    I'm currenlty watching Alex the Analyst's SQL tutorial and everytime I create a table, close, and restart SQL, the table is not found by SQL. When I use a statement such as FROM the table doesn't appear in the list of selectable objects. When I type it manually, the table_name has red lines under it indicating that it's an invalid object name. When I run the code I get the error message above. I tried refreshing the database and the Intellisense to no avail. Let me know if I should post the code for the table and thanks in advanced.

  • Total up transactions

    I am using SQL Server Management Studio v18.

    I have an original transaction that might get adjusted either by amount or by commission%.

    I want a query to total up the transactions based on ID and company and I want to keep the original transaction's TransType.

    I have full understanding on how to aggregate columns but I don't know how to keep the TransType.

    Original table:

    ID TransType Amount Commission Commission% Company Notes
    555 RE 500 125 25% Company A Original Transaction
    555 RT -400 -100 0% Company A Adjustment
    666 RA 500 125 25% Company A Original Transaction
    666 AD 0 -100 -20% Company A Adjustment
    666 RN 200 40 20% Company B Original Transaction

    Final result:

    ID TransType Amount Commission Commission% Company
    555 RE 100 25 25% Company A
    666 RA 500 25 5% Company A
    666 RN 200 40 20% Company B