Answered

Discover the answers to your questions at Westonci.ca, where experts share their knowledge and insights with you. Get quick and reliable answers to your questions from a dedicated community of professionals on our platform. Discover detailed answers to your questions from a wide network of experts on our comprehensive Q&A platform.

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 your visit. We're dedicated to helping you find the information you need, whenever you need it. We appreciate your time. Please revisit us for more reliable answers to any questions you may have. Discover more at Westonci.ca. Return for the latest expert answers and updates on various topics.