Answered

IDNLearn.com is committed to providing high-quality answers to your questions. Our community provides accurate and timely answers to help you understand and solve any issue.

There was a robbery from the ATM at the bank where you work. Some unauthorized withdrawals were made, and you need to help your bank find out more about those withdrawals. But the only information you have is that there were more than 1 withdrawals and that all of them were done in 10-second gaps.
You have a table customer_transactions with the following columns:
id - the unique transaction ID;
customer_name - the name of the customer of the bank;
transaction_time - the date and time when this transaction was made;
transaction_amount - the amount of money involved in this transaction.
You need to retrieve all the customer_names whose any two consecutive transactions have a 10-second gap from each other. It is guaranteed that no two people have the same name, so equal customer_names correspond to the same person.
Example
For given table customer_transactions
id customer_name transaction_time transaction_amount
1 Lillian Nelson 2017-01-01 10:10:15 10
2 Susan Moore 2017-01-01 11:11:11 20
3 Kian Lawrence 2017-01-01 12:12:12 10
4 Lillian Nelson 2017-01-01 10:10:20 30
5 Lillian Nelson 2017-01-01 10:10:30 40
6 Susan Moore 2017-01-01 11:11:21 50
the output should be
customer_name
Susan Moore
Explanation of the example:
Let's consider all customers:
Lillian Nelson did three transactions at 2017-01-01 10:10:15, 2017-01-01 10:10:20, and 2017-01-01 10:10:30, so the gaps between her transactions are 5 and 10 seconds respectively. Since not all the gaps are 10 seconds long, she cannot be a robber.
Susan Moore did two transactions at 2017-01-01 11:11:11 and 2017-01-01 11:11:21, so the gap between her transactions is 10 seconds. Since all the gaps are 10 seconds long, she possibly can be a robber.
Kian Lawrence did only one transaction, so he cannot be a robber.

Sagot :

Following are the SQL query commands to the given question:

Query:

select customer_name /*using inner select query  */

from

(

/* select column names*/

 SELECT  customer_name,

       transaction_time,

       next_transaction_time,

       TIME_TO_SEC(TIMEDIFF(next_transaction_time, transaction_time)) as difference

FROM    (   SELECT  customer_name,

                   transaction_time,

                   (   SELECT  MIN(transaction_time)  

                       FROM    customer_transactions T2

                       WHERE   T2.customer_name = T1.customer_name

                       AND     T2.transaction_time > T1.transaction_time

                   ) AS next_transaction_time

           FROM    customer_transactions T1

       ) AS T

) X

/*Using group by clause with min and max method*/

GROUP BY customer_name HAVING MIN(difference) = 10 and MAX(difference) = 10

Explanation of query:

  • In this question, Subqueries, group by clause, with the max and min method is used which purpose can be defined as follows:
  • It is utilized for returning information which is used to better limit the data to be found in the primary query.
  • It returns no or more rows through one or more tables or views of the database.
  • It organizes rows into groups depending on its values for one or more columns.
  • Grouping is usually utilized to use some kind of aggregate function for every group.
  • The function MIN() returns the lowest value of the column chosen.
  • The function MAX() returns the highest number in the column chosen.

Learn more:

brainly.com/question/1765746

Thank you for using this platform to share and learn. Don't hesitate to keep asking and answering. We value every contribution you make. Find clear answers at IDNLearn.com. Thanks for stopping by, and come back for more reliable solutions.