力扣sql简单篇练习(二十九)

力扣sql简单篇练习(二十九)

1 富有客户的数量

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

SELECT count(distinct customer_id) rich_count
FROM Store
WHERE amount>500

1.3 运行截图

2 有资格享受折扣的用户

2.1 基本题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

2.2 示例sql语句

CREATE FUNCTION getUserIDs(startDate DATE, endDate DATE, minAmount INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT count(distinct user_id) user_cnt
      FROM Purchases
      WHERE amount>=minAmount 
      AND time_stamp between date_format(startDate,'%Y-%m-%d %H:%i:%s') and  date_format(endDate,'%Y-%m-%d %H:%i:%s')
  );
END

2.3 运行截图

3 查找可享受优惠的用户

3.1 题目内容

3.1.1 基本题目信息

3.1.2 示例输入输出

3.2 示例sql语句

CREATE PROCEDURE getUserIDs(startDate DATE, endDate DATE, minAmount INT)
BEGIN
     SELECT distinct user_id
     FROM Purchases
     WHERE amount>=minAmount AND 
     time_stamp between date_format(startDate,'%Y-%m-%d %H:%i:%s') and date_format(endDate,'%Y-%m-%d %H:%i:%s')
     ORDER BY user_id asc;
END

3.3 运行截图