首页 > 其他分享 >leetcode 1341 电影评分

leetcode 1341 电影评分

时间:2023-06-02 12:11:18浏览次数:51  
标签:评分 1341 mr1 mr2 id user movie leetcode select

leetcode 1341 电影评分

(
    select u1.name as results from Users u1 
    left join(
        select mr1.user_id, count(mr1.rating) as c1 from MovieRating as mr1
        group by mr1.user_id
        having c1 = (
            select max(p.c2) from Users as u2
            left join (
                select mr2.user_id, count(mr2.rating) as c2 from MovieRating as mr2
                group by mr2.user_id
            ) as p
            on u2.user_id = p.user_id
    )) p2
    on u1.user_id = p2.user_id
    where p2.user_id is not null
    order by u1.name asc
    limit 0,1
)
union all
(
    select m.title as results from Movies m
    left join (
        select mr4.movie_id, avg(mr4.rating) as d1 from MovieRating mr4 
        where EXTRACT(MONTH FROM mr4.created_at) = '02'
        and EXTRACT(YEAR FROM mr4.created_at) = '2020'
        group by mr4.movie_id 
        having round(d1,2) = (
            select round(max(p3.d2),2) from 
            (
                select mr3.movie_id, avg(mr3.rating) as d2 from MovieRating mr3 
                where EXTRACT(MONTH FROM mr3.created_at) = '02'
                and EXTRACT(YEAR FROM mr3.created_at) = '2020'
                group by mr3.movie_id 
            ) p3
        )
    ) p4
    on m.movie_id = p4.movie_id
    where p4.movie_id is not null
    order by m.title asc
    limit 0,1
)

 

==

 

(
    select u.name as results from Users u 
    left join(
        select mr1.user_id, count(mr1.rating) as umr from MovieRating as mr1
        group by mr1.user_id
    ) p1
    on u.user_id = p1.user_id
    where p1.user_id is not null
    order by p1.umr desc, u.name asc
    limit 0,1
)
union all
(
    select m.title as results from Movies m
    left join (
        select mr2.movie_id, avg(mr2.rating) as mmr from MovieRating mr2 
        where EXTRACT(MONTH FROM mr2.created_at) = '02'
        and EXTRACT(YEAR FROM mr2.created_at) = '2020'
        group by mr2.movie_id 
    ) p2
    on m.movie_id = p2.movie_id
    where p2.movie_id is not null
    order by p2.mmr desc, m.title asc
    limit 0,1
)

 

==

标签:评分,1341,mr1,mr2,id,user,movie,leetcode,select
From: https://www.cnblogs.com/carlzhang19/p/17451402.html

相关文章

  • leetcode 65. 有效数字 66. 寻找两个正序数组的中位数
    leetcode65.有效数字66.寻找两个正序数组的中位数65.有效数字难度困难295有效数字(按顺序)可以分成以下几个部分:一个小数或者整数(可选)一个'e'或'E',后面跟着一个整数小数(按顺序)可以分成以下几个部分:(可选)一个符号字符('+'或'-')下述格式之一:至少一位数字,后面跟着一个......
  • 图解LeetCode——98. 验证二叉搜索树
    一、题目给你一个二叉树的根节点root,判断其是否是一个有效的二叉搜索树。有效二叉搜索树定义如下:节点的左子树只包含小于当前节点的数。节点的右子树只包含大于当前节点的数。所有左子树和右子树自身必须也是二叉搜索树。二、示例2.1>示例1:【输入】root=[......
  • LeetCode/叶值的最小代价生成树
    给你一个正整数数组 arr,考虑所有满足以下条件的二叉树:每个节点都有0个或是2个子节点。数组 arr 中的值与树的中序遍历中每个叶节点的值一一对应。每个非叶节点的值等于其左子树和右子树中叶节点的最大值的乘积。在所有这样的二叉树中,返回每个非叶节点的值的最小可能总......
  • LeetCode 96.不同的二叉搜索树
    1.题目:给你一个整数n,求恰由n个节点组成且节点值从1到n互不相同的二叉搜索树有多少种?返回满足题意的二叉搜索树的种数。示例1:输入:n=3输出:5示例2:输入:n=1输出:1来源:力扣(LeetCode)链接:https://leetcode.cn/problems/unique-binary-search-trees著作权归领扣网络所有......
  • LeetCode 343.整数拆分
    1.题目:给定一个正整数 n ,将其拆分为k个正整数的和( k>=2 ),并使这些整数的乘积最大化。返回你可以获得的最大乘积 。示例1:输入:n=2输出:1解释:2=1+1,1×1=1。示例 2:输入:n=10输出:36解释:10=3+3+4,3× 3× 4=36。来源:力扣(LeetCo......
  • 图解LeetCode——102. 二叉树的层序遍历
    一、题目给你二叉树的根节点root,返回其节点值的层序遍历。(即逐层地,从左到右访问所有节点)。二、示例2.1>示例1:【输入】root=[3,9,20,null,null,15,7]【输出】[[3],[9,20],[15,7]]2.2>示例2:【输入】root=[1]【输出】[[1]]2.3>示例3:【输入】root=[]......
  • [leetcode每日一题]5.31
    1130. 叶值的最小代价生成树提示中等324相关企业给你一个正整数数组 arr,考虑所有满足以下条件的二叉树:每个节点都有 0 个或是 2 个子节点。数组 arr 中的值与树的中序遍历中每个叶节点的值一一对应。每个非叶节点的值等于其左子树和右子树中叶节点的最大值的乘积。在所有这......
  • 二刷Leetcode-Days08
    栈与队列:/***20.有效的括号*@params*@return*/publicbooleanisValid(Strings){Deque<Character>deque=newLinkedList<>();for(inti=0;i<s.length();i++){charch=s.charAt......
  • 图解LeetCode——146. LRU 缓存
    一、题目请你设计并实现一个满足 LRU(最近最少使用)缓存约束的数据结构。实现LRUCache类:LRUCache(intcapacity)以正整数作为容量 capacity初始化LRU缓存intget(intkey)如果关键字key存在于缓存中,则返回关键字的值,否则返回-1。voidput(intkey,intva......
  • leetcode
    1python常用函数1.1排序函数原地排序nums.sort()不改变原列表有返回值new=sorted(nums)importfunctools#一维数组排序nums=[2,1,3,4,5]defcompare_udf(x,y):#x-y升序#y-x降序returnx-y##python2nums.sort(cmp=compar......