美国华人网FuninUSA_唐人社区_北美华人论坛:找礼品卡,找折扣,找报价,找工作,找内推,找项目,找股票

 找回密码
 立即注册
  • 章子怡的好后妈人设崩塌? 继女被曝和奶奶生活
  • 好着呢!杨幂刘恺威参加女儿班级活动 一家人同框
  • 美图:我们用户大多是“白富美” 不愁赚钱
  • 网红面包店深夜发声明 承认面粉过期
  • 手机以换壳为本?华为余承东:没有创新才这么干
  • 腾讯二把手悄然交接 一人拿到大笔钱一人掌握更大权
  • 那些恐怖电影中的角色生活中原来长这样
  • 《神奇女侠》女神新手办亮相:脸崩了 但美腿还能玩一年
  • 再进华夏中原 小米之家郑州第二店正式开业
  • 中国人的睡眠:西藏最幸福 海南最可怜
    Logo1-800-PetMeds Free Shipping $49Take $10 Off Your First Order w/code: SAVE10 - 234 x 60
    ASICS AmericaPagoda Piercing Banner 234x60Sierra Trading Post
    搜索
    查看: 3252|回复: 9

    内推面经 -SQL, recruiter发过来的面试题- 唐人社区|北美华人论坛

    [复制链接]

    15

    主题

    30

    帖子

    71

    积分

    新手上路

    Rank: 1

    积分
    71
    QQ
    发表于 2016-9-13 20:16:31 | 显示全部楼层 |阅读模式
    分享到:
    {$content}

    唐人社区-北美华人论坛-内推面经版-SQL, recruiter发过来的面试题


      JobHunting
    标 题: SQL, recruiter发过来的面试题


    有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)

    We work with large datasets, and are always performance conscious since
    extended processing times will impact our time to market. Keep this in mind
    as you answer the following questions:

    There is a table defined as:
    CREATE TABLE [Positions](
    [load_id] [int] NOT NULL,
    [acct_cd] [varchar](20) NOT NULL,
    [acct_num] [varchar](255) NULL,
    [sec_id] [varchar](50) NOT NULL,
    [long_sht_cd] [varchar](3) NOT NULL,
    [sedol] [varchar](15) NULL,
    [isin] [varchar](15) NULL,
    [cusip] [varchar](9) NULL,
    [sec_type] [varchar](8) NULL,
    [sec_name] [varchar](100) NULL,
    [currency_cd] [varchar](3) NULL,
    [total_holding] [decimal](18, 4) NULL,
    [mkt_price] [float] NULL,
    [datetime_stamp] [datetime] NULL,
    CONSTRAINT [pk_Positions] PRIMARY KEY CLUSTERED (
    [load_id] ASC,
    [acct_cd] ASC,
    [sec_id] ASC,
    [long_sht_cd] ASC )
    )

    This table holds account positions data that are appended to multiple times
    a day
    There are currently some 24 million rows in the table. Every time we append
    additional positions we add approximately 32,000 entries to this table, and
    all 32,000 entries will have the same load_id. The load_id is incremented by
    one each time we load a batch of 32,000 entries (i.e. the first 32K entries
    have load_id=1, the next 32K has load_id=2, etc...). The datetime_stamp
    field shows the time at which the entries were loaded and is the same for
    all 32K entries in a single load.


    How would you efficiently retrieve the first set of positions for the
    current day given the above table definition?

    Example:
    Today, positions were loaded into this table at 8am, 10am and 3pm. At 5pm
    today we want to know what positions were loaded at 8am since that is the
    first load that occurred today. Note that for any given day, there can be
    different number of loads and the times that the loads occur will vary.

    --
    【中国海淘拼单总群】36382164
    回复 百度谷歌雅虎搜狗搜搜有道360奇虎

    举报

    36

    主题

    82

    帖子

    156

    积分

    注册会员

    Rank: 2

    积分
    156
    QQ
    发表于 2016-9-13 21:51:22 | 显示全部楼层
    JobHunting
    标  题: SQL, recruiter发过来的面试题


    有兴趣做不? 我琢磨着, 出题的人是在寻找最优解 :)

    We work with large datasets, and are always performance conscious since
    extended processing times will impact our time to market. Keep this in mind
    as you answer the following questions:

    There is a table defined as:
    CREATE TABLE [Positions](
                [load_id]           [int]                  NOT NULL,
                [acct_cd]         [varchar](20)   NOT NULL,
                [acct_num]      [varchar](255)             NULL,
                [sec_id]            [varchar](50)   NOT NULL,
                [long_sht_cd]   [varchar](3)     NOT NULL,
                [sedol]              [varchar](15)   NULL,
                [isin]                 [varchar](15)   NULL,
                [cusip]              [varchar](9)     NULL,
                [sec_type]       [varchar](8)     NULL,
                [sec_name]     [varchar](100) NULL,
                [currency_cd] [varchar](3)     NULL,
                [total_holding] [decimal](18, 4) NULL,
                [mkt_price]      [float]               NULL,
                [datetime_stamp] [datetime]   NULL,
    CONSTRAINT [pk_Positions] PRIMARY KEY CLUSTERED (      
    [load_id] ASC,
                [acct_cd] ASC,
                [sec_id] ASC,
                [long_sht_cd] ASC )
    )

    This table holds account positions data that are appended to multiple times
    a day
    There are currently some 24 million rows in the table. Every time we append
    additional positions we add approximately 32,000 entries to this table, and
    all 32,000 entries will have the same load_id. The load_id is incremented by
    one each time we load a batch of 32,000 entries (i.e. the first 32K entries
    have load_id=1, the next 32K has load_id=2, etc...). The datetime_stamp
    field shows the time at which the entries were loaded and is the same for
    all 32K entries in a single load.   


    How would you efficiently retrieve the first set of positions for the
    current day given the above table definition?

    Example:
    Today, positions were loaded into this table at 8am, 10am and 3pm.  At 5pm
    today we want to know what positions were loaded at 8am since that is the
    first load that occurred today.  Note that for any given day, there can be
    different number of loads and the times that the loads occur will vary.

    --

    28

    主题

    106

    帖子

    169

    积分

    注册会员

    Rank: 2

    积分
    169
    QQ
    发表于 2016-9-13 23:50:17 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    up
    --

    30

    主题

    100

    帖子

    161

    积分

    注册会员

    Rank: 2

    积分
    161
    QQ
    发表于 2016-9-14 00:20:36 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    这根本不是SQL的题目,倒像是DW的构架,按照date进行sharding

    或者搞个index table,只存loadid和timestamp,每个batch只有一行,join起来很容易

    这个问题的层次好水

    --

    9

    主题

    227

    帖子

    240

    积分

    中级会员

    Rank: 3Rank: 3

    积分
    240
    QQ
    发表于 2016-9-14 00:55:36 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    波士顿的一家金融公司?
    --

    27

    主题

    120

    帖子

    177

    积分

    注册会员

    Rank: 2

    积分
    177
    QQ
    发表于 2016-9-14 01:31:56 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    你的这两道题我都做过,当时agency联系我的,花了一下午做完,发回去,就再没消息

    --

    24

    主题

    99

    帖子

    151

    积分

    注册会员

    Rank: 2

    积分
    151
    QQ
    发表于 2016-9-14 01:36:19 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    Mind sharing your answer here? 大家共同进步 :)

    【 在 alexander44 (ALEXANDER) 的大作中提到: 】
    : 你的这两道题我都做过,当时agency联系我的,花了一下午做完,发回去,就再没消息
    : 了



    --

    22

    主题

    95

    帖子

    133

    积分

    注册会员

    Rank: 2

    积分
    133
    QQ
    发表于 2016-9-14 01:46:27 | 显示全部楼层
    JobHunting
    标  题: Re: SQL, recruiter发过来的面试题


    你不会做就直接说不会做
    直接请教这里的大牛帮你做出来
    还尼玛最优解
    sql有个鸡毛最优解
    【 在 peace1 (peace1) 的大作中提到: 】
    : Mind sharing your answer here? 大家共同进步 :)



    --

    23

    主题

    1165

    帖子

    2320

    积分

    金牌会员

    Rank: 6Rank: 6

    积分
    2320
    QQ
    发表于 2016-9-22 19:38:06 | 显示全部楼层
    看帖要回,回帖才健康,在踩踩,楼主辛苦了!

    24

    主题

    1193

    帖子

    2365

    积分

    金牌会员

    Rank: 6Rank: 6

    积分
    2365
    QQ
    发表于 2016-10-6 06:25:51 | 显示全部楼层
    鼎力支持!!
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    美国华人网|唐人社区|什么值得买FunInUSA.net发布的内推面经 -SQL, recruiter发过来的面试题- 唐人社区|北美华人论坛帖子由网友提供或转载于网络,若发布的内推面经 -SQL, recruiter发过来的面试题- 唐人社区|北美华人论坛侵犯了您的权益,请联系我们.
    Sasa.com

    Copyright ©2011 FunInUSA.NET All Right Reserved.  Powered by Discuz! X3.0 小黑屋

    本站信息均由会员发表,不代表美国华人网FunInUSA|唐人社区的立场,如侵犯了您的权利请发帖投诉  技术支持: 美国华人网FunInUSA|唐人社区

    安全联盟认证 安全联盟认证

    快速回复 返回顶部 返回列表